Problems with Excel automation in PowerShell

Question

Hi fellows,

I’m trying to automate the refreshing of data in my Excel Reports. Here the script:

$ExcelApp = new-object -ComObject Excel.Application
$ExcelApp.Visible = $false
$ExcelApp.DisplayAlerts = $false

$Workbook = $ExcelApp.Workbooks.Open("RefreshTest.xlsx", 3, $false, 5, $null, $null, $true)
$Workbook.RefreshAll()
$Workbook.Save()

$ExcelApp.Quit()

The script is fine, but when I try to run it directly it shows me the following error:

Method invocation failed because [System.__ComObject] doesn't contain a method named 'Save'.
At line:7 char:15
+ $Workbook.Save <<<< ()
    + CategoryInfo          : InvalidOperation: (Save:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

Curiously, if I run just the top part of the script (before the Save method), then call the Save method after, it works fine.

Could you help me? It was tested against PowerShell V1 and PowerShell V2 (the bits installed with Win7 Beta).

Best regards, Rafa

Answer 1

Your logic seems to be fine, and this works for me (XP SP2 and PowerShell v2).

Just before you call Save(), just for fun, add this line to see what gets printed:

$Workbook|Get-Member *Save*

I tried with a blank file. Is your file big perhaps? I wonder if you have a large file, and maybe need to add a "Start-Sleep -sec 5" maybe...

Answer 2

This script works fine for me as well as written.

I would agree with Marco that this seems like a race condition error. The error message is weird, but the fact that it works when you have human pause between the load/refresh and save portions of your script points to a long load/refresh time causing your problem.

Try the "Start-Sleep" function/cmdlet that Marco suggested and see if that works.