Save versus SaveAs in PS 5.1

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

  • Author
    Posts
  • #102419

    Ramon Tan
    Participant

    I am running the ff. script – its purpose is to blank out some cells in an EXCEL file 'MyFile.xlsx' and Save it to the same:
    1 – $Excel = New-Object -Comobject Excel.Application
    2 – $Excel.Visible = $True
    3 – $Excel.DisplayAlerts = $False
    4 – $wbookn = "C:\Users\XXC\Documents\MyFile.xlsx"
    5 – $book = $Excel.Workbooks.Open($wbookn)
    6 – $sheet = $book.Worksheets.Item("wks1")
    7 – $sheet.Activate() | Out-Null
    8 – $sheet.Range("B2:F1001").ClearContents()
    #
    # Line 9 works, but why does '$book.Save' create an error?
    #
    9 – $book.SaveAs($wbookn,51)
    #
    Line 9 above works just fine. But when I changed it to
    $book.Save
    it fails to save the contents. Additionally I get these extra lines of messages on the screen:
    OverloadDefinitions
    ——————-
    void Save ()

    0
    from Powershell. I must be missing something with the use of the Save method within Powershell.
    Would be grateful for any tips, advice or pointers to the right answer.
    Many thanks.

  • #102430

    James Crompton
    Participant

    It looks like what is happening is you are not calling the Save() method but outputting the reference to the save method which is showing you the list of valid overloads for Save.
    Change line 9 to
    $book.Save()
    The () indicates that it is a method which should be called with no parameters and I imagine it will work.
    -James

    • #102436

      Ramon Tan
      Participant

      Many thanks Jim ...

      I dug further into the MS Technet wesbites and it turns out there is no Save() method for Worksheets — but there is a Save() method for Workbooks.

      https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet_methods.aspx
      https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook_methods.aspx

      I do not really know the root cause ... but perhaps that may explain why I got those 3 or 4 extra lines with the "Overload Definitions" message. As suggested I changed Line 9 of the code in the Forum post to:

      $book.Save()

      and it executed without errors. But when I attempted to open the resultant file, it (EXCEL) gives an error message about the file type being the wrong type or is corrupted. This is the same behavior as another earlier problem which I posted before this one, and for which the solution that works (thanks to another Forum participant) is:

      $Excel.Workbooks.Item(1).SaveAs(,51) <<< the '51' is to Save to .XLSX Open XML format with Item(1) being the worksheet in question. Many thanks for your inputs, they have led me to more interesting insights and discoveries. Much appreciated! Best,

You must be logged in to reply to this topic.