Forum Discussion
Migrate Excel files with a lot of links to SharePoint Online?
This is quite problematic for our users too, a lot of users got caught out by creating spreadsheets in OneDrive sync folder on their machine, linking there and then sharing the files. The Excel has a link reference to C:\user\<name>\One Drive - <Tenant Name>\Test.xlsx etc But of course the person using the file does not have access that persons c: drive so the <name> bit doesn't exist on their local drive.
Links to https:\\<onedrive location> tend to also be a little slow.
So its tricky, plus links like this can easily break spreadsheets and cause errors. I'm not sure I can count the times that someone has come to me asking me to check their spreadsheet because they think it might be wrong but they can't see where the issue is. Normally its a link or copying down etc.
If it was me, I would use the new feature built into Excel called PowerQuery, on the data tab, using SharePoint Folder link and avoid using links all together ... but this is a lot of work to change, but the long term benefits are huge.
This effectively queries the other spreadsheet, using a simple interface, so no need for vloopup or linking or calculations in the spreadsheet ... all calculations can be done in the query, so they are repeatable and difficult to break. Plus as a bonus, if you copy the query which it builds it can be pasted into PowerBI and you can create dashboards very quickly that are available anywhere.
I am facing the same problem...
1) when i upload the files onto SharePoint and try use them the links break or point to the wrong location
2) when i try and sync using onedrive - they also point to wrong location and even if I update the location to the sharepoint location of a file, it doesn't update correctly when i open the linked file.
This is quite problematic for our users too, a lot of users got caught out by creating spreadsheets in OneDrive sync folder on their machine, linking there and then sharing the files. The Excel has a link reference to C:\user\<name>\One Drive - <Tenant Name>\Test.xlsx etc But of course the person using the file does not have access that persons c: drive so the <name> bit doesn't exist on their local drive.
Links to https:\\<onedrive location> tend to also be a little slow.
So its tricky, plus links like this can easily break spreadsheets and cause errors. I'm not sure I can count the times that someone has come to me asking me to check their spreadsheet because they think it might be wrong but they can't see where the issue is. Normally its a link or copying down etc.
If it was me, I would use the new feature built into Excel called PowerQuery, on the data tab, using SharePoint Folder link and avoid using links all together ... but this is a lot of work to change, but the long term benefits are huge.
This effectively queries the other spreadsheet, using a simple interface, so no need for vloopup or linking or calculations in the spreadsheet ... all calculations can be done in the query, so they are repeatable and difficult to break. Plus as a bonus, if you copy the query which it builds it can be pasted into PowerBI and you can create dashboards very quickly that are available anywhere.
- Thanks Stephen, I'll give that a try!
Hi,
Here are screenshots of our tool at work!
Excel URLs are reported on, and if needed it will replace incorrect portions of the URL (replacement provided by user).
Chris
