Converting EXCEL files in Powershell

This topic contains 2 replies, has 2 voices, and was last updated by  Ramon Tan 1 week ago.

  • Author
    Posts
  • #102386

    Ramon Tan
    Participant

    I am running the ff. script in order to convert 'file1.xls' to 'file2.xlsx' via a SaveAs method:
    $Excel = New-Object -Comobject Excel.Application
    $Excel.Visible = $True
    $Excel.DisplayAlerts = $False
    $Excel.Workbooks.Open("C:\Users\XXC\Documents\file1.xls")
    $Excel.Workbooks.item(1).SaveAs("file2.xlsx")

    The script runs and completes without any error messages. When I try to open the resulting file — 'file2.xlsx' — EXCEL 2007 (what I have running) gives the error message that the file type or extension is invalid or corrupted. I note that the file 'file1.xls' was the result of an export in print mode from MS-ACCESS. There is ONLY ONE WORKSHEET in 'file1.xls' to begin with.
    Moreover: when I run EXCEL directly on 'file1.xls' and then do a SaveAs (to file type .XLSX), it works just fine. Why then is it failing when I run the same steps in the PS script shown? I have PS 5.1, Windows 7 64-bit Ed. platforms.
    Would be highly grateful for any advice, tips or hints towards resolving this dilemma. Thanks!

  • #102397

    Joel Sallow
    Participant

    See here, under the FileFormat parameter:
    https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-saveas-method-excel

    For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

    So just changing the filename doesn't generally mean Excel will save in a different format.

    Here is a list of valid values for the file format:
    https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlfileformat-enumeration-excel

    It looks like you should be able to do this to save in the proper format:

    $Excel.Workbooks.Item(1).SaveAs("FilePath\Workbook.xlsx", 51)
    • #102416

      Ramon Tan
      Participant

      My sincerest thanks, Mr Joel Sallow.
      I have read the URL reference documentation and followed your solution and it works perfectly.
      Your response was very helpful and informative/educational!
      Best,

You must be logged in to reply to this topic.