Migrate Excel files with a lot of links to SharePoint Online?

[Julian Revheim Askeland]

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

[#2]

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

[#3]

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.

[#4 · Accepted solution]

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.

[#5]

Thanks Stephen, I'll give that a try!

[#6]

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

[#7]

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

[#8]

This is the Excel file after the tool fixes the link (http://oldserver to http://contoso)

[#9]

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)

[#10]

Did anyone find a good way to handle this?

[#11]

---

@reditguy wrote:

Did anyone find a good way to handle this?

Hi reditguy,

We ended up buying a product called LinkFixer Advanced by Linktek.

https://www.linktek.com/

// Julian

[#12]

Hi,

as document migration is controlled process (I hope ;)) you always know what is/was your source location and what will be new location. In case that you use ReplaceMagic you have two options:

- you fix your files before you move them to new location. Here you'll basically specify what is source location (like \\serverABC) and what will be new location (like, https://company.sharepoint.com) and let ReplaceMagic do the job

- or you copy everything to SP.Online and then do the same thing again

Of course, there are variations like you copy documents from source location to some temporary storage, fix them (again search for source, replace with new location) and upload them to SP.Online

Key here is that you know what you copying and where you are copying (source-target relation). 

ReplaceMagic is used by tons of customers and majority of migrations are now going direction SharePoint.Online (occasionally to SharePoint on-prem) so with RM can achieve your targets. 

Plus price tag is very, very competitive to other tools :). You can check prices over www.replacemagic.com/store.aspx

[#13]

Microsoft uses our product internally (for the past 3 years in multiple areas internally), as well as NASA (3 research centers), Intel, HP (deployed globally), Ford (used by 12K site collection admins). We have over 40K users using our product, the SharePoint Essentials Toolkit.

Our product fixes links within MS Excel formulas, PDF, MS Word, web pages, web parts etc. and can be found from our website at https://www.qipoint.com.

It also can handle digitally signed documents correctly so not to modify them and invalidate signatures, MS Office Online will keep link integrity when opened with MS Word/Excel online, etc..

[#14]

Thank you Chris....I've spoken to your team but it's incredibly expensive compared to others.  

[#15]

The guy's trying to get a simple solution to a problem and it become Battle of the Vendors.

Have you read this?

https://support.office.com/en-us/article/open-or-change-source-workbooks-of-external-references-link...
What about doing a find/replace of the link across multiple workbooks? Requires macro and the code can be found on multiple sites/forums... here's 1:

https://excel.tips.net/T005598_Searching_Through_Many_Workbooks.html 

[#16]

So there are 2 strategies to apply:
• SharePoint Online
o Migration needs to be done using the SharePoint Migration Tool (SharePoint Migration Tool for SharePoint and OneDrive - Migrate to SharePoint and OneDrive | Microsoft Docs). Can use an Azure Databox (Migrate your file share content to SharePoint using the Azure Data Box - Migrate to SharePoint and OneDrive | Microsoft Docs), but should only use if your data is in a remote location. Keyless AFD in <city name> will help with data transfer.
o Pro’s
 Cost included in the O365 licenses depending on the plan you have. SharePoint Online Plan 2 is certainly the best option.
 Document versioning and access control.
 Easier sharing and collaboration.
 Enterprise search.
 Keyless Azure Front Door (AFD) here in <city name> for SharePoint Online & OneDrive for Business.
 Can be synced to device using the OneDrive for Business sync tool,
o Con’s
 No UNC pathname.
 End user change management.
• Azure Files
o Pro’s
 Easy migration with lift & shift of the file system.
 Replicate your UNC pathnames providing limited end user impact.
 Depending on the amount of data, you could transfer the data using Azure Databox.
o Con’s
 More expensive. Cost’s to consider would be for a sync server, snapshot and of course the storage cost.
 If deterministic path, you would need to use Azure VPN gateway or ExpressRoute. Additional cost.

[#17]

@reditguy 

Finally, which tools you choose for your migraion? LinkFixer Advanced, ReplaceMagic or others? Thanks.

[#18]

Linkfixer - worked like a champ.

[#19]

@Julian Revheim Askeland 

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.