Linking Excel Office On The Web and Local Excel Workbooks

Hi Zeglash,

Thanks for the clarification and sorry for creating the confusion.

I understand you want to export data from Excel for the web to a local Excel workbook, in this case as well, you have to use Excel desktop application to accomplish the task.

To export data from Excel workbook that is stored in SharePoint online, open that workbook in desktop application and then click File>Info>Copy path.

Clicking “Copy path” will give you a path in this format: https://Tenant.sharepoint.com/sites/SiteName/Shared%20Documents/FileName.xlsx?web=1

With this starting point, all you need to do is remove the ?web=1 query string parameter at the end of the URL, and you have the path to the workbook that Power Query needs.

If you are importing data to Excel, you can use Get Data tab. Choose “From Web” as the data source in the Get Data menu and paste this URL into the Dialog.

Click Ok and it will open a small Window, select Organizational Account, click Sign in and enter your work or school account. This will open a navigator, where you can select tables and worksheets, and can start manipulating and analyzing your Excel data.

At the time of refreshing the data, you will be required to open the source file in desktop application. Reference: Refresh data connected to another workbook.

Let me know if you need any help.

Regards,

Neha

Hi Zeglash,

The error "Access to the resource is forbidden" you are getting is not related to permissions in SharePoint online or Excel. The error is very common and can occur while connecting SharePoint list to Excel or Power BI etc.

Please try the following method to clear permission in Excel and then check again:

1.     In Excel, click Data>Get Data>Data Source Settings, click Clear all permissions.

2.     In Get Data tab, click From Other sources>From Web.

3.     In the Access web content box, select Organizational account, choose the site collection under drop-down, Sign in> Connect. When you try to connect your data source, it will allow you to re-enter your credential.

Regards,

Neha

The proposed solution worked for me.

Thanks!

Just wondering why getting data from excel 4web to a standard workbook has to be so complicated...

Neha,

I appreciate your quick response. I'm not sure this is the solution I am looking for. Just so I'm clear, are you suggesting that I use the "Open in Desktop App" button for my excel online sheet and then just link the workbooks when they're both open in the desktop app? This doesn't work as once you close the desktop source workbook, anything that is updated online doesn't update in the linked local workbook. 

Are you saying there is no way to pull data from excel online workbooks?

Thank you

Hi Zeglash,

Welcome to the Answers community.

From your post, we understand that you have one workbook stored in SharePoint online and other on your local computer and you want to link both, so any data changed in the workbook stored in cloud should reflect in local workbook, correct?

To link both the workbooks, you should use Excel desktop application. We can’t link directly from Excel for the web.

Once you open both the workbook in Excel desktop application, you can use Data tab to connect to external data.

1. In the source workbook, click on the Data tab, then Get Data > From File > select From Workbook.
2. In the Excel Browse dialog box, browse for or type a path to the file that you want to query.
3. Click Open. If your source workbook has named ranges, the name of the range will be available as a data set.

Reference: Import data from external data sources (Power Query)

When you are connected to an external data source, you can also perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

Besides, you mentioned “ I have attempted to use "From Web" and "Get Data > From SharePoint Online List" and have been unsuccessful with both.”, this option helps you connect to your SharePoint list, since you want to connect Excel workbooks, this option will not work in this scenario.

If you need any help, let me know. Have a nice day and stay safe😊

Regards,

Neha