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?

Modulate

You say that it does it for a large number of files, do you create a new Excel com object for each file? Or do you open Excel, and then loop through files with a Open/Refresh/Close file cycle before exiting Excel and releasing the com object? – Picaroon

I create it, do my loop, then close it. The example I gave is condensed without the loop. The whole script is pretty long. – Modulate

Does Excel need credentials to access the network location? Does a credentials prompt appear when Visible is true? – Gibbeon

Excellent question, but no. It does require authentication, but it's handled via a normal domain ACL. (So no login prompts or username/PW input). – Modulate

Add your Start-Sleep 30 statement between RefreshAll() and Save() – Overlook

My scenario is not the same as yours as even for a local file I can repro the issue; therefore I am not sure if the workaround worked for me will also work for you: it seems simply adding sleep between $excelworkbook.RefreshAll() and $excelworkbook.Save() works. – Unitarian

Very interesting... I actually end up with an error as soon as I call open($workbook)... Maybe it has to do with excel version as well then. What are you testing with @wangzq? I am using powershell v4 and excel 2014. – Modulate

@Ethan, I simply added a simple database query from my local sql server, then created two copies of the excel file on both local and a network share. – Unitarian

@wangzq, that's interesting... I just tried it out and you're right it did work! That's great! Want to post it as an answer so that I can mark you as correct? – Modulate

@MathiasR.Jessen Since you had the suggestion first do you want to add an answer here? – Peripatetic

@Peripatetic I've added an answer – Overlook

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
    }
}

Overlook

Answer 2

I would add a DoEvents block:

[System.Windows.Forms.Application]::DoEvents() 

This will allow the queue to cycle through anything you've told Excel to do & then back to the script execution. Another thing would be to set UserControl = false so that Excel is not simply hidden, but is clearly out of the user's ability to respond to events.

Lastly, there may be something about setting Visible after you've set other properties - it may be the case that Excel responds to the Visible event by toggling a few other things (don't remember off-hand, but something in the back of my brain says this is the case, or used to be).

Cissie

DoEvents() did not change it for me. – Found

It looks like the OP accepted that a sleep before saving solved the problem for them. DoEvents should be the same thing, in that it releases Excel's place in the queue, but I can't explain the difference in the two. That said: exact same problem? If so, I'd been thinking that it might be about authentication anyway (timing out maybe). – Cissie

I ended up with something like: for($ctr = 1; $ctr -le 5; $ctr++) { try { write-output "attempting to resave to $outfile..." $wb.SaveAs($outfile, $xlSLSXType) $errorlevel = 0 break } catch [System.Runtime.InteropServices.COMException] { if($_.Exception.ErrorCode -eq 0x80010001) { sleep 0.25 } else { throw } } } – Found

Answer 3

In my case, I used Powershell to open an Excel file, run a macro and then I wanted to check the data and edit it if I needed to. After that I saved the file. My problem was that I didn´t press Enter key after typing something in a cell, so apparently it was still excecuting a process, which threw that exception. I solved it using a try catch block, with a popup message to finish the cell edition, and then executing again the .SaveAs().

Protean

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Jed