June 13, 2018 at 9:51 pm #102419
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
it fails to save the contents. Additionally I get these extra lines of messages on the screen:
void Save ()
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.
June 14, 2018 at 2:31 am #102430
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
The () indicates that it is a method which should be called with no parameters and I imagine it will work.
June 14, 2018 at 5:48 am #102436
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.
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:
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.