Office 365 Workbooks.open from SharePoint Online opens blank file

Question

Our company is using Office 365 Pro Plus on Semi-Annual update channel. Lately we've been updated from version 1708 to 1803.

We are using some automation macros than open files from our SharePoint online tenant. Since the update, a call of Workbooks.open("https://xxxxxx.sharepoint.com/sites/.../xxx.xlsx") now prompts for an O365 user and password every time, and don't use the one used as Excel account.

It opens a "blank" workbook (see screenshot) :

Workbooks.open() on local documents is still working as before, only opening files from SP Online has this problem.

I tried to switch to monthly channel on a computer, but the problem persists. Opening the file manually using the link is working.

Here is a sample code we're using :

Sub Transfert_SST_Copy()

Dim Tbl As ListObject
Dim NewRow As ListRow
Dim Data As ListRow
Dim Wb As Workbook

Set Wb = Workbooks.Open(Filename:="https://xxxxx.sharepoint.com/:x:/r/sites/XXX/AMTB%20RFQ%20costing/00%20General/Overview%20RFQs/Cost%20computations%20simple%20overview/2018%20AMTB_Cost_computations_overview_simple.xlsm?d=w8929b5112ed7496bb25d82b0bfc472c5&csf=1&e=PADrRt", ReadOnly:=False)
' Here Excel opens a "Blank" Workbook, so everything after that is giving an error
Set Tbl = Range("CostCalcOverview").ListObject
Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

NewRow.Range.Offset(0, 1).Resize(1, ThisWorkbook.Worksheets("Transfert").Range("A275:AW275").Count).Value = ThisWorkbook.Worksheets("Transfert").Range("A275:AW275").Value

End Sub

lambs lambs · Accepted Answer · 2019-05-17T10:22:24

Hi I had the same issue - and it was getting to me as it seemed such an easy thing! The problem is with your filename link.

 Set Wb = Workbooks.Open(Filename:="https://xxxxx.sharepoint.com/:x:/r/sites/XXX/AMTB%20RFQ%20costing/00%20General/Overview%20RFQs/Cost%20computations%20simple%20overview/2018%20AMTB_Cost_computations_overview_simple.xlsm?d=w8929b5112ed7496bb25d82b0bfc472c5&csf=1&e=PADrRt", ReadOnly:=False)

The filename link you used is directly from SharePoint when you click "Share" or "Copy link", but what resolved this for me is by actually opening the SharePoint file in Desktop Excel. Then click File. You are then shown the "Info" page which shows the File Name - if you look directly below the filename (above where it says "Protect Workbook") there looks to be like breadcrumb links (folder names from SharePoint separated by >>) - click that section and click "Copy path to clipboard"

Replace your Filename link in your code with this and it will work!

Office 365 Workbooks.open from SharePoint Online opens blank file

1 Answers