Converting EXCEL files in Powershell
I am running the ff. script in order to convert 'file1.xls' to 'file2.xlsx' via a SaveAs method:
$Excel = New-Object -Comobject Excel.Application
$Excel.Visible = $True
$Excel.DisplayAlerts = $False
The script runs and completes without any error messages. When I try to open the resulting file — 'file2.xlsx' — EXCEL 2007 (what I have running) gives the error message that the file type or extension is invalid or corrupted. I note that the file 'file1.xls' was the result of an export in print mode from MS-ACCESS. There is ONLY ONE WORKSHEET in 'file1.xls' to begin with.
Moreover: when I run EXCEL directly on 'file1.xls' and then do a SaveAs (to file type .XLSX), it works just fine. Why then is it failing when I run the same steps in the PS script shown? I have PS 5.1, Windows 7 64-bit Ed. platforms.
Would be highly grateful for any advice, tips or hints towards resolving this dilemma. Thanks!
See here, under the FileFormat parameter:
For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.
So just changing the filename doesn't generally mean Excel will save in a different format.
Here is a list of valid values for the file format:
It looks like you should be able to do this to save in the proper format:
My sincerest thanks, Mr Joel Sallow.
I have read the URL reference documentation and followed your solution and it works perfectly.
Your response was very helpful and informative/educational!
You must be logged in to reply to this topic.