Powershell: Error while calling the SaveAs Method of some Office Applications

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.

[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:

$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.

$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

$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

Advertisment to support michlstechblog.info

Add a comment »One comment to this article

  1. Powershell: Export Excel Workbook as csv file | Michls Tech Blog

Time limit is exhausted. Please reload CAPTCHA.

Original Theme by Schiy · Powered by WordPress