Using VBA to open workbook from Sharepoint opens blank or is inaccessible from within sub

[phanzom]

Hello Excel experts, first of all, thanks for this fantastic board full of advice, with which I was able to solve dozens of problems!

This is my first post, and the requirement looks easy, but then again....

I need to open an Excel file from Sharepoint and copy a range of cells to the active workbook. That being said, this must work from outside the company i.e. without authentication. For this purpose, I created a link to the file in Sharepoint that allows Anyone to open the file without signing in:

This link looks like this:

https://somecompany.sharepoint.com/...1EhOPsXXefMuUBQtOEnuYRR8vaOMfxvV7civ?e=OLW6Xs

I can open the link via VBA using:

• ActiveWorkbook.FollowHyperlink Address:=(lnk)

and this opens the workbook, but then I can’t access the opened workbook from within the sub, getting the following Error: Script out of range, and: ‘Excel can’t do this because a dialogue box is open’. In fact, I think the link only opens AFTER the sub has finished. I tried to build in a delay, to wait for the link to open, but that does not work.

If I use

• Workbooks.Open(lnk)

then Excel will open a blank, white page, totally empty, even without the cell borders. I have followed some advice about disabling DDE, but that didn't fix it.

If I use the regular link to the file, e.g. something like

https://somecompany.sharepoint.com/sites/Sales/Shared Documents/Projects/versionTable.xlsx?web=1

then i get a prompt to sign-in with Microsoft, which I can’t use because the users are not from our company.

So either I use use the Hyperlink approach, and learn how to access the file from a sub, or fix the Workbooks.Open approach opening the file correctly. Any help is very much appreciated.

Many thanks
phanzom