PowerShell Script to Bulk-Change Excel File Formats

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

About these ads

5 Responses to “PowerShell Script to Bulk-Change Excel File Formats”


  1. 1 Stanley de Boer May 31, 2013 at 11:46 am

    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.

    • 2 Dmitry Sotnikov June 3, 2013 at 4:35 am

      Thanks Stanley! Good points.

      How did you reference the Excel COM object enumeration?

      • 3 Stanley De Boer June 3, 2013 at 6:41 pm

        You can reference any enumuration like this.

        [Microsoft.Office.Interop.Excel.XlFileFormat]::xlXMLSpreadsheet

      • 4 Dmitry Sotnikov June 10, 2013 at 6:15 pm

        Thanks Stanley! I’ve updated the script per your feedback. Thanks again for your help!


  1. 1 Dew Drop – May 31, 2013 (#1,558) | Alvin Ashcraft's Morning Dew Trackback on May 31, 2013 at 12:33 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




My Recent Tweets

Legal

The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer - WSO2 or anyone else for that matter. All trademarks acknowledged.

© 2007-2014 Dmitry Sotnikov

May 2013
M T W T F S S
« Feb   Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031  

Follow

Get every new post delivered to your Inbox.

Join 2,329 other followers

%d bloggers like this: