Linking Excel Office On The Web and Local Excel Workbooks

Question

I am currently working on a shared excel workbook online between multiple people. I am also creating a local excel workbook that needs to pull information from the workbook online. How do you link these? Can you link them both ways so an online workbook pulls from a local file? The shared excel file is accessed through a SharePoint site. I also have Microsoft Teams available if I can save a collaborate excel workbook there and connect through that.

I have attempted to use "From Web" and "Get Data > From SharePoint Online List" and have been unsuccessful with both. Both result in a "Access to the resource is forbidden" error message.

Example: If Person 1 in the online file changes A1 from "Yes" to "No", the local workbook linked to that cell (='WorkbookOnline'A1) would automatically changed from "Yes" to "No"

Thanks!

Answer 1

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

Answer 2

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

Answer 3

Hi Zeglash,

Yes, this is correct. In Excel for the web, there is no option to import data from another notebook, as you must have noticed, there is no Get Data function. To link the workbooks, we have to open the workbooks in desktop application and then use Get Data function.

Every time you wish to update the data, you must use Excel desktop client and the source book should be open. For more information on refreshing, see Refresh data connected to another workbook.

We understand the inconvenience, but there are some differences between using a workbook in the browser and in Excel app. Some features may work differently than the desktop app. I believe this is one of the differences in Excel for the web and desktop app.

We appreciate your co-operation and understanding.

Kind regards,

Neha

Answer 4

Neha, 

I think there might still be some slight confusion in what I'm trying to accomplish. I'm not trying to import data into a workbook online. I'm trying to export from the online workbook. I'm attempting to have a desktop version of excel import from excel online. 

Is there anyway to accomplish this?

Thank you

Answer 5

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