SOLVED

Excel External Table Links Only Work on Local Device

Copper Contributor

Howdy all,

 

This is my first time posting in this community. Because I'm unsure of exactly the extent of the scope of my particular inquiry I'll try to include all relevant details.

 

I have a macro enabled "Project Info" excel workbook that functions as a template for whenever I create a new project file. Whenever a new project folder gets created the workbook gets copied into the new project folder. This workbook has a tab that contains a table that is linked from an external excel file that contains business contacts. This enables the project info excel file to do lookups on the business contacts table while keeping the contact file separate. That way additions to the list of contacts can be done in a single location. All of this is located on the business OneDrive folder on my local device.

 

Now, everything works fine when I am working from my local device; tables update as expected. Great. However, the project file link to the contact table uses the local file path to locate it. This means when I, or another employee opens the project info file from a different device, the external table reference doesn't work anymore. The path doesn't exist. I get a "[DataSource.Error] Could no find a part of the path..." error.

 

I've done some looking around on how to fix this issue. I thought maybe linking the table through the web version of excel would work. However, when I attempt this it only links the absolute range. So if I were to add to the contact table the reference wouldn't dynamically expand. I suppose I could just extend the absolute range beyond what should ever be needed, several hundred rows, but I don't know if that will slow down link update time, and I want to make sure there isn't a more correct method first.

 

If I could somehow reference the table in the cloud, then I shouldn't have local reference issues, but I haven't figured out how to do that yet. Any pointers are appreciated.

 

2 Replies
best response confirmed by HotSteel (Copper Contributor)
Solution
You should be able to create a Powerquery connection to that table, but you must use a slightly different way to get the file to connect to, read this: https://exceloffthegrid.com/power-query-connect-onedrive-sharepoint/
Thank you, I was able to get it to work!
1 best response

Accepted Solutions
best response confirmed by HotSteel (Copper Contributor)
Solution
You should be able to create a Powerquery connection to that table, but you must use a slightly different way to get the file to connect to, read this: https://exceloffthegrid.com/power-query-connect-onedrive-sharepoint/

View solution in original post