9

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?

Matt
  • 45,022
  • 8
  • 78
  • 119
Ethan
  • 787
  • 1
  • 8
  • 28
  • 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? – TheMadTechnician Jun 09 '15 at 00:47
  • I create it, do my loop, then close it. The example I gave is condensed without the loop. The whole script is pretty long. – Ethan Jun 09 '15 at 01:55
  • Does Excel need credentials to access the network location? Does a credentials prompt appear when `Visible` is true? – acelent Jun 09 '15 at 11:40
  • 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). – Ethan Jun 09 '15 at 18:06
  • Add your `Start-Sleep 30` statement between `RefreshAll()` and `Save()` – Mathias R. Jessen Jun 13 '15 at 16:04
  • 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. – wangzq Jun 14 '15 at 00:36
  • 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. – Ethan Jun 14 '15 at 16:47
  • @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. – wangzq Jun 15 '15 at 09:02
  • @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? – Ethan Jun 15 '15 at 14:18
  • @MathiasR.Jessen Since you had the suggestion first do you want to add an answer here? – Matt Jun 15 '15 at 14:32
  • @Matt I've added an answer – Mathias R. Jessen Jun 15 '15 at 15:28

2 Answers2

3

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
    }
}
Community
  • 1
  • 1
Mathias R. Jessen
  • 157,619
  • 12
  • 148
  • 206
1

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).

David T. Macknet
  • 3,112
  • 3
  • 27
  • 36
  • `DoEvents()` did not change it for me. – Scott Stafford Mar 01 '23 at 19:23
  • 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). – David T. Macknet Mar 02 '23 at 20:51
  • 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 } } }``` – Scott Stafford Mar 09 '23 at 19:17