I have a script that i thought would work to convert Excel (.xls) files to Excel 2007/2010 (.xlsx).  However everytime i try to do the "saveas" I keep getting Exception calling "SaveAs" with "2" Argument(s): "SaveAs method of workbook class failed"

I have googled/binged and added the folder "Desktop" per one suggestion on my Windows 7 x64 running Office 2010 machine.

The script is below (some lines commented out as i was testing even tried changing to csv to see if it was my excel, with no luck)

#$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]

#$xlFixedFormat = xlWorkbookNormal
$xlFixedFormat = xlCSV


$excel = New-Object -ComObject excel.application
$excel.visible = $false
$folderpath = "C:\TEST\*"
$filetype ="*xls"
Get-ChildItem -Path $folderpath -Include $filetype |
ForEach-Object `
{
$path = ($_.fullname).substring(0,($_.FullName).lastindexOf("."))
"Converting $path to $filetype..."
$workbook = $excel.workbooks.open($_.fullname)

#$workbook.saveas($path,$xlFixedFormat::xlWorkbookDefault)

$workbook.saveas($path, $xlFixedFormat)
$workbook.close()
}
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

It's your $xlFixedFormat.  First, you had it pointing to the csv format.  Second, you need to call the enumeration like this:

$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault

$excel = New-Object -ComObject excel.application
$excel.visible = $true
$folderpath = "c:\test\*"
$filetype ="*xls"
Get-ChildItem -Path $folderpath -Include $filetype | 
ForEach-Object `
{
$path = ($_.fullname).substring(0,($_.FullName).lastindexOf("."))
"Converting $path to $filetype..."
$workbook = $excel.workbooks.open($_.fullname)

$workbook.saveas($path, $xlFixedFormat)
$workbook.close()
}
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

The above script is working fine when I run on a machine with Office 2010 but it is giving me the following error when I run on office 2007. Any advise?

Unable to find type [Microsoft.Office.Interop.Excel.XlFileFormat]: make sure that the asse
mbly containing this type is loaded.
At C:\Temp\temp\FileConvertFromxls.ps1:1 char:63
+ $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat] <<<< ::xlWorkbookDefault
    + CategoryInfo          : InvalidOperation: (Microsoft.Offic...el.XlFileFormat:String
   ) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

TIA,

I checked my Excel 2007 and found that:

$xlWorkbookDefault = 51
$xlFixedFormat = $xlWorkbookDefault

Seems to work. Just make sure your Default is set to xlsx.

OldDog

Thanks,

It worked

Hi,

    I know this is an old thread. However, I am looking for a script to save the excel file from .xlsx to .html format. I used your code and change the value for $xlWorkbookDefault from 51 to 44 (xlHtml - webpage format). The script works great. However, it did not export any of the workbook's style (css for cell border, background, etc). Do you have any suggestions on how to fix this? And advises are greatly appreciated. Thanks,

Convert single and bulk xls file into xlsx format easily try Office Upgrade software. Its converting XLs into Xlsx format. Get complete information following: http://www.systoolsgroup.com/office-upgrade.html