Get data from another Excel file located on company's SharePoint

Resolved · Low Priority · Version 365

Mon 25 Sep 2023, 23:41 Delegate Eric said...

Eric has attended:
PowerPoint Intermediate Advanced course
Excel Intermediate course

Get data from another Excel file located on company's SharePoint

I'm trying to get data from an Excel spreadsheet located on my company's SharePoint site (Office 365 for business) but am getting an error message. The message is: "Unable to connect...the supplied folder path must be a valid absolute path."

Under the Data tab, I clicked Get Data > From File > From Folder.
Then, copied the first part of the URL to the folder name (as shown in this video, https://www.youtube.com/watch?v=-XE7HEZbQiY )
Then clicked Open.
Result = error message listed above.

Can you help?

Tue 26 Sep 2023, 11:38 Trainer Martin said...

RE: Get data from another Excel file located on company's ShareP

Hi Eric,

Thank you for your question to the forum.

If you try to connect to a SharePoint file using Get Data and connect to a SharePoint folder, you will end up importing all files from this folder. Then you will need to use Power Query to locate the Excel file which will take too long. A more efficient and targeted approach would be to connect to an Excel file in SharePoint. Here's how to do it:

1. Open SharePoint and open the Excel file. Then open the Excel file in the Excel desktop app.

2. In the Excel desktop app, go to File > Info

3. Click Copy Path

4. Open Notepad and paste the link.

5. Delete the last bit of the link which reads ?Web=1

6. Select and copy the link

7. Go to Get Data > From Other Sources > Web

8. Paste the link into the URL box and click OK

9. Select ‘Organisation Account’ - check the root path is correct

10. If asked, log in with your Office 365 credentials

11. This will load the connected data into Excel from the Excel file in SharePoint

I hope this helps. Please let me know how you get on

Kind regards
Martin Sutherland
(IT Trainer)

Tue 26 Sep 2023, 18:52 Delegate Eric said...

RE: Get data from another Excel file located on company's ShareP

That worked, Martin. Thank you! With my selection following the process you enumerated, one of the "source" worksheets now appears in my destination workbook.
I have several follow-up questions.
1) Should I use the same 11-step process to link each worksheet that I need from the source workbook to my destination workbook? (Or, is there a faster way to link all of a source workbook's worksheets to the destination workbook?)
2) Do changes in the source worksheet update the destination worksheet? (The destination worksheet doesn't update for me. If I add or change values in the source worksheet, nothing changes in the destination worksheet - even after clicking the "Refresh All" button in the Data > Queries & Connections tab.
3) What impact do changes in the destination worksheet have on the source worksheet? (I would expect none - but want to make sure.) In the destination worksheet, I need to fill in missing data in the table.

Tue 26 Sep 2023, 11:42 Trainer Jens said...

RE: Get data from another Excel file located on company's ShareP

Hi Eric,

Thank you for the forum question.

It is not straight forward to connect to SharePoint.

I have watched the video from your question and it all looks good.

I assume it cannot be a security issue.

I have experienced that removing the right part of the path (URL) can be the issue.

I have done what you want several times. If you what the video below you will find different ways of doing it, and for many they use the From WEB solution.

https://www.youtube.com/watch?v=XuLnSYjmsJo

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Do a fast scroll

In big Excel databases with many records, you can move down thousands of rows super-fast as follows: hold down Shift then click on the scrollbar somewhere below the scrollbar handle. This will move you way down the sheet without your having to use the scrollbar up/down arrows or drag on the scrollbar handle.

View all Excel hints and tips