How to open Excel file from SharePoint online site in Excel Power Query

Question

Trying to make my query refreshable by any user with access to the SharePoint site where the Excel file is stored.  However I get error saying "You cannot open this location using this program." when I try to browse to the folder using Windows Explorer dialog.

Also cannot find anyway to open the library in Explorer from either the SharePoint site itself or OneDrive site.

Used to be possible on classic SharePoint sites.

Answer 1

Hi James Griffiths,

Thanks for your updates. The link copied in modern site library is share link of the file. Therefore, it doesn’t work for power query. I would suggest you copy file path in Excel File>Open and you will get the URL of the file here by right click the file in SharePoint and choose copy file path to clipboard. Just like what post in my previous reply.

Besides, we can open the excel file by click Data>Get Data>From file>From workbook, type/copy URL  of the SharePoint site **** in the address bar of File Explorer dialog box. You will get a list of document library in the site here. As you can see in screenshot below, you can choose your excel file here and open them in power query.

 

Hope these can help.

Regards,

Clark

Answer 2

Hi James Griffiths,

I go through the post. SharePoint online is web-based, and you need to input right URL of the file open it in power query. You can view all your workbooks stored in SharePoint in File>Open if you have sign in Excel with your account and connect to SharePoint service.

Find the workbook here, Right click it and copy its path to clipboard. You will get a URL look like text below:

https://contoso.sharepoint.com/sites/<sitename>/Shared%20Documents/<workbookname>.xlsx?web=1

please remove ?web=1 in this URL and you will get a URL of the file. In Excel>Data>From web, copy the URL here and click next. You will need to enter your account and password to access it. after finishing typing them, you can click next and open your workbook in SharePoint in Excel power query.

Hope these can help you.

Regards,

Clark

Answer 3

Thanks for the reply.  That is what I do for SharePoint classic and works fine.  SharePoint online however gives me an error saying "Access to the resource is forbidden" or if I try to browse to the location it says you cannot open location with an application.

url I get is https://###.sharepoint.com/:x:/r/sites/.../Shared%20Documents/...Level.xlsx?d=w022e964f7cc7485fb2a2a23bb4ebef80&csf=1&e=OXNQXG  (parts removed for privacy)

Regards

James

Answer 4

Hi Clark,

Many thanks that worked.  I still got the access error to start and then just had to change the data source options to be Organisational account (instead of Windows) and sign in.

James