Eric has attended:
Excel Intermediate course
PowerPoint Intermediate Advanced 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?
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)
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.
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
