powershell for excel in sharepoint online using workbooks.checkout error "We're having trouble connecting to the server"

-1

I have an excel in SharePoint Online, and this excel has a cell with reference to another file, therefor I want to auto-refresh it, and the only way currently available is to use a script to open, refresh, and save.

Since the file is in SPO I also need to checkout and check-in it again. the error is caused at the checkout line.

I am using the code as in this question and the relevant code is

$xl = New-Object -ComObject Excel.Application; 
...
If ($xl.workbooks.CanCheckOut($i))
...
$xl.workbooks.checkout($i)

Where $i is the filename.

And the promted error is "We're having trouble connecting to the server. If this keeps happening, contact your help desk."

In the event logs:

Failed refreshing the workbook https://tenant.sharepoint.com/somepath/somefoile.xlsx The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED))

I must state that changing to save&close, while setting to to demand checkout to false in the document library, it works like a charm.

Also doing all this manually, open the file, click the checkout in the yellow line, and check-in works no problems.

I've also tried to let the PS sleep for 15s before checkout.

UPDATE:

I've also tried to checkout before opening the file, got this one Failed refreshing the workbook https://.......xlsx The remote procedure call failed. (Exception from HRESULT: 0x800706BE)

excel
powershell
refresh
sharepoint-online
asked on Stack Overflow Dec 17, 2018 by bresleveloper • edited Dec 17, 2018 by bresleveloper

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0