Forum Discussion

Copper Contributor

Nov 25, 2018

Tool to auto fix broken excel links after migration to SharePoint online

Hi everyone, We are migrating one of our customer's file share the SharePoint online, and we getting big pushback from their finance department, as they have a lot of linked excel files. file lin...

Copper Contributor

Jun 18, 2021

Lei Zhou Lei Zhou

Set the base address for the links in a workbook

By default, unspecified paths to hyperlink destination files are relative to the location of the active workbook. Use this procedure when you want to set a different default path. Each time that you create a link to a file in that location, you only have to specify the file name, not the path, in the Insert Hyperlink dialog box.

Follow one of the steps depending on the Excel version you are using:

In Excel 2016, Excel 2013, and Excel 2010:
1. Click the File tab.
2. Click Info.
3. Click Properties, and then select Advanced Properties.
4. In the Summary tab, in the Hyperlink base text box, type the path that you want to use.

Note: You can override the link base address by using the full, or absolute, address for the link in the Insert Hyperlink dialog box.

NBS42

Brass Contributor

Jun 18, 2021

Thanks, Lei. Either the version of Excel that was used to do the linking was super old or the users who did the linking chose to do absolute instead of dynamic path linking. Either way, the link addresses are effectively hardcoded into the Excel files.

If you are suggesting there's a way to update the links in the source files so that they become dynamic, and then when the files are migrated from NTFS to SharePoint, the dynamic linking finds the files in the SharePoint folder paths -- well, that would be cool. Is there a way to do that automagically so a user doesn't have to manually update the links? If a user has to manually update the links, then we would plan to just have them do that on the SharePoint end of things and deal with the pain after the files are migrated. That's a very unpopular thing to ask the users to do.

Thanks for your advice.

JamesT100
Copper Contributor
Sep 16, 2022
NBS42

Is that modern tools to perform migration for linked Excels? We have many linked Excel and would like to migrate to SharePoint Online. Thanks a lot.
- Oliver_Dracus
  Copper Contributor
  Sep 16, 2022
  JamesT100ReplaceMagic can help with fixing links after migration to SharePoint.
  In general, you have relative or absolute links and, if we assume, that absolute links is:
  
  \\ServerABC\Folder\SubFolder\Filename
  
  and that in the future, on SharePoint, it will be:
  
  https://company.sharepoint.com/sites/site/DocLib/folder/subfolder/filename
  
  you can just set that ReplaceMagic searches for \\ServerABC and replaces it with http://company.sharepoint.com/sites/site/DocLib. Of course, tool can align slashes or backslashes.
  
  Final result will be https://company.sharepoint.com/sites/site/DocLib/Folder/SubFolder/filename and of course, all link where we have match with your search string will be updated.
  
  Similar applies for relative links. For example, you have something like:
  
  \Folder\Subfolder\Filename
  
  you can replace it by searching for \Folder and making replacement with https://company.sharepoint.com/sites/site/DocLib/Folder
  
  With this approach all files in selected location will get this change.
  
  To get initial idea about your links you can do scan of files after migration (of course, you can do that also before migration) and ReplaceMagic will create snapshot of everything found (btw. you do not need to buy the tool to do scanning) which will be starting point to see what kind of links you will need to fix.
  
  BR,
  Oliver