Forum Discussion
Solved
Migrate Excel files with a lot of links to SharePoint Online?
Hi,
We are in a process of moving our file server to SharePoint Online.
Now we have a department that has taken a master-class in linking Excel documents together.
From my web searches I'm unable to find a good and flexible solution to handle links in excel files while we move.
We might end up in a situation where we will need to link between different document libraries.
Unfortunately, this is not a few files with one or two links in them, but hundreds of files with up to 30 links in each.
Also, a lot of files are linked to other files with links.
Does anyone have any experience with this? Is there any good tools to handle links for a change like this?
Best regards,
Julian Revheim Askeland
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.
This might help someone
you dont need to manually update links in 2023
Migrate with sharegate to retain file metadata and previous version history
Then let your users SYNC the doc libraries to their file explorer and then copy the excel file from their mapped drives to the SYNCed sharepoint doc library, (overwriting the sharegate migration copy of the files).
As long as excel is installed, file explorer will AUTO UPDATE all links in the excel files.
Hi Julian
one tool that might help is ReplaceMagic (check: www.replacemagic.com). ReplaceMagic supports changes in all Office document formats so this might help you here also.
In case of any questions you can always contact us.
Btw. we work natively with SharePoint (you can connect to it directly from ReplaceMagic). More infos: https://www.replacemagic.com/HowToUseSharePoint.aspx
BR,
Oliver
(I work for ReplaceMagic)
Hi
Disclaimer: I work for QiPoint!
We offer a tool that will find, report on, and fix the links in MS Excel formulas and also in cells. The Excel files need to be in SharePoint on-premises or SharePoint Online. There is an option in the tool to turn this on under Links->Manage-Options, this works well and is in use by many of our customers.
The tool also provides the worksheet and the cell coordinates in the reports so you know where links are present.
SharePoint Broken Link Manager - designed to fix links after a SharePoint Migration
Hope that helps!
Chris
- Did you ever find a solution to this?
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.
- Let's see what others can say here, but if there is not a tool that allow you to replace those links I think you have to consider to make some custom development to do it
