Author Posts

June 13, 2018 at 10:10 am

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
$Excel.Workbooks.Open("C:\Users\XXC\Documents\file1.xls")
$Excel.Workbooks.item(1).SaveAs("file2.xlsx")

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!

June 13, 2018 at 12:10 pm

See here, under the FileFormat parameter:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-saveas-method-excel

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:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlfileformat-enumeration-excel

It looks like you should be able to do this to save in the proper format:

$Excel.Workbooks.Item(1).SaveAs("FilePath\Workbook.xlsx", 51)

June 13, 2018 at 6:31 pm

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!
Best,