Today I had to find a way to change file formats for a lot of files. Here at Jelastic we use JIRA for bug-report tracking. Every week I am getting tons of automated reports from JIRA to send around to our partners and so on. The problem is that JIRA exports reports in some weird html format with xls extention, and although Windows Excel can open it (after displaying ‘the file format and extension do not match’ warnings) – Macs cannot.
Opening and resaving lots of files manually was not an option – PowerShell was. 🙂
[UPDATE] I’ve made a few minor changes per feedback from Stanley – now properly managing file extansions and formats.
Here’s the script that you can reuse if you have a similar problem to solve:
############################
# PowerShell script to open all Excel files in a folder
# and re-save them in proper format
# (c) Dmitry Sotnikov
############################
# create COM object to use Excel
$objExcel = new-object -comobject excel.application
$objExcel.Visible = $True
# open the files to re-save one by one
dir D:\myfolder\*.xls | ForEach-Object {
$doc = $objExcel.WorkBooks.Open($_.FullName)
# Save in new format and with new extension
# Format codes can be found here:
# http://msdn.microsoft.com/en-us/library/office/ff198017.aspx
$doc.SaveAs(“$($_.Directory)\$($_.BaseName).xlsx”,
[Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
$doc.close()
}
$objExcel.Quit()
$objExcel = $null
I wrote a script to do this last week. So I have two suggestions to make it better.
I used ChangeExtension to easily change the extension as I was moving to .xlsx to .xml.
$newFilename = [System.IO.Path]::ChangeExtension($filename, “xml”)
If you use the enumuration for file types it makes it more obvious what format you are saving it as.
Thanks Stanley! Good points.
How did you reference the Excel COM object enumeration?
You can reference any enumuration like this.
[Microsoft.Office.Interop.Excel.XlFileFormat]::xlXMLSpreadsheet
Thanks Stanley! I’ve updated the script per your feedback. Thanks again for your help!
Hi. I am very new to PowerShell… Appreciate if you could provide support to write a code to open 50 Excel files in a folder, refresh the calculations, save and close one after the other (or all together if that is even possible).
if you wanted to change an .xlsm file to .xlsx file would this open a file and allow a macro to run if it was set to run on workbook being opened?