powershellscriptingpowershell-4.0

Method invocation failed because [System.__ComObject] does not contain a method named 'Close' in Powershell v4.0

this is my first StackOverflow question, but I will try to keep the community standards in mind..

I am running Office 2013 on Win7 Pro and PoSh v4.0. Upon execution of the subsequent script, I receive the following error: "Method invocation failed because [System.__ComObject] does not contain a method named 'Close'."

$xl = New-Object -comobject Excel.Application
$xl.Visible = $false
$xl.DisplayAlerts = $false
$filepath = "C:\Users\Username\Desktop\Projects\EDCautomation\attach\"
$wb1 = $xl.Workbooks.Open((Join-Path $filepath "File-Template.xlsx"))
sleep 5
$wb1.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

In keeping with the standard of presenting the shortest code that will reproduce the error, I developed the above code down from a larger automation

Powershell 4.0 and Excel 2013 | Bug | Work-a-round seemed to be getting at the same point. I followed the thread/culture solution offered by XXInvidiaXX and the same error was generated.

Problems with Excel automation in PowerShell had an answer by Roy that pointed to a timing error, but I inserted a Sleep command and tried running the open and close commands separately, both to no avail.

Any help, especially underlying theory, is greatly appreciated.

Solution

  • Even though the arguments to Workbook.Close() are all optional, all the examples on MSDN provide at least one (the SaveChanges argument), leading me to believe that PowerShell does not recognize the method signature when you don't provide any arguments.

    You might have better luck with:

    $wb1.Close($false)

    or

    $wb1.Close($false,$null,$null)

    Substitute with $true to save any changes you may have made