excelvbapowershellcomexcel.application

Test if Excel has finished its startup / shutdown sequence

I'm using PowerShell to automate some excel tasks via COM, via a PowerShell script like so: 

$xl = new-object -ComObject Excel.Application
sleep 5
DO THINGS...
$xl.quit()
sleep 5
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
[GC]::collect()

My issue is that if I omit the first call to sleep, Excel won't be ready to perform tasks that require add-ins, and the call to $xl.quit() doesn't work, and the process fails to quit, as evidenced by: 

PS > Get-Process EXCEL

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    784     104   104008     109380   944     3.21   5996 EXCEL

Likewise, if I omit the second call to sleep, excel doesn't have time to complete shutting down, and eventually this results in a dialog box stating "Microsoft Excel has stopped working..." and the next time Excel starts up, another dialog box states "Microsoft Excel failed to shut down properly, Start in safe mode?"

function XLtest {
    $xl = new-object -ComObject Excel.Application
    $xl.quit()
    $null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
    [GC]::collect()
}

for($i=0;$i -lt 10; $i++){ XLtest } # generates multiple errors

Obviously these are problems that I want to avoid, but the heuristic solution of calling sleep 5 could still fail on ocasion. Is there any way to detect when excel has completed it's start-up sequence and is ready to issue commands (xl.quit() in particular) and when the shut-down sequence is complete (i.e. when it's ok to call [System....Marshal]::ReleaseComObject($xl)?

Solution

  • While your exact environment and situation is impossible to reproduce without an add-in like the DO THINGS... one mentioned, there are members of the Excel Application object that should be able to be exploited to gain some sense of the application state.

    One that seems a likely candidate is the _Application.CalculationState property. If a full recalculation was forced then a Do While...: DoEvents: Loop was processed, a pseudo-timer could be pausing the powershell script while the Application.CalculationState was not xlDone much the same as an Internet.Explorer object is waited upon until it finishes receiving its web page.

    $xl = new-object -ComObject Excel.Application
$xl.Calculate
do while $xl.CalculationState <> xlDone: $xl.DoEvents: Loop
$xl.quit()
... (do more things?)

    A recalulation can also be forced with the _Application.CalculateBeforeSave property if you are saving before closing (and subsequently exiting).

    Another good candidate is the simpler _Application.Ready property.

    $xl = new-object -ComObject Excel.Application
do while not $xl.ready: $xl.DoEvents: Loop
$xl.quit()

    See Application Members (Excel) for a full list of potential properties where a 'state' can be determined. More information at Developer’s Guide to the Excel 2010 Application Object.