Excel Convert .xls to .xlsx

Question

_{Wednesday, January 5, 2011 10:17 PM}

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()

All replies (7)

_{Thursday, January 6, 2011 2:19 AM ✅Answered}

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()

http://twitter.com/toenuff
write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""

_{Thursday, May 5, 2011 1:28 PM}

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,

Mohammedu

_{Thursday, May 5, 2011 2:26 PM}

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

_{Thursday, May 5, 2011 3:58 PM}

Thanks,

It worked

Mohammedu

_{Thursday, February 14, 2013 1:45 AM}

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,

_{Monday, October 21, 2013 10:44 AM}

If you are looking for a external tool, then visit this informative blog which helpful to upgrade xls into xlsx format. http://www.modernghana.com/news/491920/1/ict-watch-can-i-upgrade-office-2003-to-office-2010.html

_{Tuesday, October 21, 2014 9:23 AM}

Hi colsoh, instead of an excel automation you can try using this .NET component for excel files.

Add-Type -Path 'C:\Test\GemBox.Spreadsheet.dll'
[GemBox.Spreadsheet.SpreadsheetInfo]::SetLicense('FREE-LIMITED-KEY')
$input_path = 'C:\Test\Sample.xlsx'
$output_path = 'C:\Test\Sample.html'
$workbook = [GemBox.Spreadsheet.ExcelFile]::Load($input_path)
$workbook.Save($output_path)

Hope it helps.