Hi,
if you tried to save a word or excel document as another format you got an error. For example, if you want to convert a CSV File to a Excel workbook and save it in Excel 95/97/2003 format.
1 2 3 4 5 6 7 8 9 10 11 12 | [reflection.assembly]::LoadWithPartialName("Microsoft.Office.InterOp.Excel")$oExcel = New-Object -ComObject "Excel.Application"$sCSVFile="D:\temp\ImportFile.csv"$oExcel.Visible = $true$oCSVWorkbook=$oExcel.Workbooks.Add()$oExcel.Workbooks.OpenText($sCSVFile)$oCSVWorkbook.SaveAs("D:\temp\test.xls",[Microsoft.Office.Interop.Excel.XlFileFormat]::xlExcel9795)Exception calling "SaveAs" with "1" argument(s): "SaveAs method of Workbook class failed"At D:\test\CSV2Excel.ps1:29 char:20+ $xl_workbook.SaveAs <<<< ("D:\Temp\test.xls") + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation |
This error occurs because since Excel 2007 the SaveAs Methode does not support all file formats defined in [Microsoft.Office.Interop.Excel.XlFileFormat]. These formats should work:
xlOpenXMLWorkbook
xlOpenXMLWorkbookMacroEnabled
xlExcel12 – Excel 2007
xlExcel8 – Excel 95/97/2003
Use these paremeters to save your Excel file compatible for Office 95/2003:
1 | $oCSVWorkbook.SaveAs("D:\temp\test.xls",[Microsoft.Office.Interop.Excel.XlFileFormat]::xlExcel8) |
This quick and dirty hack opens a new Excel Workbook, tries to save it in all formats enumerted by XlFileFormat and lists all types where no error occurs.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | $oExcel = New-Object -ComObject "Excel.Application"[reflection.assembly]::LoadWithPartialName("Microsoft.Office.InterOp.Excel")$oExcel.DisplayAlerts=$false$oExcel.Visible = $true$oCSVWorkbook=$oExcel.Workbooks.Add()$oSupportedFileTypes=@()foreach($oFileType in ([System.Enum]::GetValues([Microsoft.Office.Interop.Excel.XlFileFormat]))){ Remove-Item -Path "D:\temp\test.xls" -Force -ErrorAction SilentlyContinue -WarningAction SilentlyContinue |Out-Null try { $oCSVWorkbook.SaveAs("D:\temp\test.xls",$oFileType)|Out-Null -ErrorAction SilentlyContinue -WarningAction SilentlyContinue $oSupportedFileTypes+=$oFileType } catch{ Write-Host $oFileType.ToString() not supported }}Write-Host -ForegroundColor Green "Supported Filetypes:"$oSupportedFileTypes$oCSVWorkbook.Close()$oExcel.Quit()Start-Sleep 1[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null$oExcel=$null |
And the same for Word
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | $oWord = New-Object -ComObject "Word.Application"[reflection.assembly]::LoadWithPartialName("Microsoft.Office.InterOp.Word")$oWord.DisplayAlerts=[Microsoft.Office.InterOp.Word.WdAlertLevel]::wdAlertsNone $oWord.Visible = $true$oDocument=$oWord.Documents.Add()$oSupportedFileTypes=@()foreach($oFileType in ([System.Enum]::GetValues([Microsoft.Office.Interop.Word.WdSaveFormat]))){ Remove-Item -Path "D:\temp\test.doc" -Force -ErrorAction SilentlyContinue -WarningAction SilentlyContinue |Out-Null try { $oDocument.SaveAs2(([ref]"D:\temp\test.doc"),([ref]$oFileType))|Out-Null -ErrorAction SilentlyContinue -WarningAction SilentlyContinue $oSupportedFileTypes+=$oFileType } catch{ Write-Host $oFileType.ToString() not supported }}Write-Host -ForegroundColor Green "Supported Filetypes:"$oSupportedFileTypes$oDocument.Close()$oWord.Quit()Start-Sleep 1[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWord)|out-null$oWord=$null |
Enjoy
Michael
One thought on “Powershell: Error while calling the SaveAs Method of some Office Applications”