none
Excel Convert .xls to .xlsx

    Question

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

    Wednesday, January 05, 2011 10:17 PM

Answers

  • 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 ""
    • Marked as answer by mtj7009 Friday, January 07, 2011 4:55 PM
    Thursday, January 06, 2011 2:19 AM

All replies

  • 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 ""
    • Marked as answer by mtj7009 Friday, January 07, 2011 4:55 PM
    Thursday, January 06, 2011 2:19 AM
  • 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 05, 2011 1:28 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

    • Proposed as answer by OldDog1 Thursday, May 05, 2011 4:00 PM
    Thursday, May 05, 2011 2:26 PM
  • Thanks,

    It worked


    Mohammedu
    Thursday, May 05, 2011 3:58 PM
  • 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,

    Thursday, February 14, 2013 1:45 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

    Monday, October 21, 2013 10:44 AM