Powershell excel refresh fails with "Call Was Rejected by Callee" when .visible=$false

Question

I've had this issue for a long time now and had just ignored it out of laziness, however I now need to find a solution. I have a script which automates refreshing a large number of excel documents. This works well and dandy, however, it fails if I have the Visible property set to false on workbooks which are stored on a network share.

To reiterate, refreshing with the visible property set to false works fine on LOCAL files, but any workbook saved on a \ location fails with an error "Call was rejected by callee". All refreshes work fine with the visible property set to true.

Here is my code :

#Create Excel COM object and set it up for use.
$excel = new-object -comobject Excel.Application;
$excel.DisplayAlerts = $false;
#If this is set to false, saving the file on a network share will fail. Reason : Unknown.
$excel.Visible = $true;
#Open workbook which should be refreshed. 
$excelworkbook = $excel.workbooks.Open($workbook);
#Refresh WB
$excelworkbook.RefreshAll();
#Save
$excelworkbook.Save();
#Quit Excel
$excel.Quit();
#Destroy COM object. (VERY IMPORTANT!!!!!)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel);

I have tried the following :

1. Adding Start-Sleep 30 between creating the excel object and setting the visible property
2. Setting visible before DisplayAlerts
3. Wishing really hard for it to just work

Any ideas?

Answer 1

It seems that RefreshAll() doesn't wait for the refresh to actually succeed in the background with Visible = $False set.

Introduce an artificial delay between RefreshAll() and Save(), like so:

$excelworkbook.RefreshAll();
Start-Sleep -Seconds 30
$excelworkbook.Save();

---

Alternatively, you might be able to force the RefreshAll() to execute synchronously by setting BackgroundQuery = $False on all query tables in your workbook, as suggested in this answer to a similar question:

foreach ($Sheet in $excelworkbook.Worksheets) {
    foreach ($QTable in $Sheet.QueryTables) {
        $QTable.BackgroundQuery = $false
    }
}