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)
User contributions licensed under CC BY-SA 3.0