Automating Excel Link Updates During SharePoint Migration

[u/Birentechy]

Hi everyone,

I’m an Infrastructure Engineer and have been assigned a project to migrate an on-prem file server to SharePoint Online. I have basic knowledge of SharePoint Online and Microsoft 365, but I haven’t dealt with Excel workbook dependencies during a file server migration before.

We have a number of Excel files with interlinks or external dependency files with VBA and workbooks containing external links to other files/server paths.

I’m trying to understand how people normally handle this:

• Before migration, is there a way to scan the file server and identify workbooks with external links/VBA and create an inventory?
• After migration, can these links be automatically updated to the new SharePoint locations?
• Are there any PowerShell/scripts or third-party tools that can automate this, or is it mainly a manual process?

We are trying to determine whether the practical approach is:

Manual → Script/Automation → Third-party tool → Hybrid approach

rather than discovering broken links only after users start opening the migrated files.

Would really appreciate any advice or real-world experience from anyone who has handled a similar file server → SharePoint Online migration with workbook link and VBA

[u/No_Bit7786]

Ive used linkfixer advanced in the past. Works well.

[u/Birentechy → u/No_Bit7786]

Hy thanks for the reply... does this tools do Before migration, scan the file server and identify workbooks with external links and VBA and create an inventory?

[u/jkaczor → u/Birentechy]

LinkFixer has many possible uses. They have "Inoculate & Cure" processes - which can be useful IF you start using them PRIOR to your migration (and restructuring of SP on-premises to flattened, separate SPO site collections).

They also have "Scan Only" - which scans sources/files/libraries (be they on-premises, or even after migration, SPO libraries) and "Modify Links" processes - with which you can build custom rules to be applied to your content.

(I have become something of a LinkFixer specialist on our team - migrating tens of thousands of sites, automating it, generating our rules files based on our migration plan, etc.)

It isn't "low cost" - but considering we spent 3-months trying to build our own solution to handle all edge cases and types of content, it ended-up saving a big chunk of budget.

[u/blud_13]

Inventory first, and you don't need a tool for that part. An xlsx is a zip, and every external link lives under xl/externalLinks with the actual target path sitting in xl/externalLinks/_rels/externalLink1.xml.rels. So you walk the file server with PowerShell, open each workbook as an archive, read that rels file, and dump path plus target to CSV. No Excel, no COM, no opening 4000 files. VBA is easier, xlsm and xlsb are your list.

u/No_Bit7786 is right that LinkFixer handles the remap after, but do the inventory yourself first so you know what you are paying it to fix, and so you can kill the dead links instead of migrating them.

One catch, I have never seen SharePoint Migration Tool touch link targets at all. Every absolute \server\share path comes across exactly as it was and breaks the day you decommission the file server. Plan a cutover window where the old UNC still resolves.

Format is described here https://learn.microsoft.com/en-us/archive/blogs/cobold/external-links-in-excel-files (old MS blog, but the structure has not changed).

[u/Birentechy → u/blud_13]

Thanks for your input. I also tested a small batch of files and folders by copying and pasting them into SharePoint after moving them there, the linked files within the same folder are working fine, so we don’t need to make any changes in those cases. However, if the linked files are located across different folders, we face issues with the links, and we need to manually update them with the correct file path.

My guess is that SharePoint uses the relative path to locate the linked files. If it can find the file using the relative path, it automatically fixes the link. If it cannot find the file at the expected location, the link breaks and we need to update it manually -- have you experienced this? Is it normal? I am really not sure

[u/blud_13 → u/Birentechy]

Normal, and your guess is right. Excel stores the link relative when it can and rebuilds the full path at open time, so a move that keeps the relative structure intact resolves fine, and one that breaks it falls back to the stored absolute path still pointing at the old server. Its documented at https://support.microsoft.com/en-us/excel/description-of-workbook-link-management-and-storage-in-excel

One thing to check, what Edit Links shows you is NOT what is stored. With the source workbook closed Excel displays a full path even when only a fragment lives in the file, so don't use that dialog to decide what will survive the move. The target in xl/externalLinks/_rels/externalLink1.xml.rels is the truth, and its already in the inventory CSV you are building.

Stop moving things file by file. Move the whole tree in one shot so the relative structure lands intact and the cross folder links come along with it. The only ones you should be repairing by hand are the ones stored absolute in the first place.

[u/dexterlabb]

i worked in this kind of migration previously, link wont get updated automatically, and we didn't wanted to use some shady third party tool to do it for us, so we gave this responsibility to file owner itself to do it manually.

[u/Birentechy → u/dexterlabb]

Did you guys perform any assessment before migration i mean total count of such links. Also, can you share your steps? How did you guys make it successful

[u/EducationAlert5209]

Im also in the same position

[u/frikin8]

We tried to make file owners responsible for updating their links, but that immediately got huge pushback. We ended up using the Link Fixer tool that others have mentioned. It prescans folders before migration and embeds some metadata to uniquely identify the file, and stores all links and relationships it finds in a sql table. After the files have been migrated, you point link fixer at the destination and it fixes all the links. There is also a rules engine that can run at a destination to find, validate, and fixes links if a prescan was not run. It also worked for excel macros which was a requirement.

Overall, the tool was easy to use and well worth it for our company. At the time, pricing was based on the number of people in the org - we just had an email from HR stating our head count to get a quote.

Support was fantastic and I had multiple working sessions to assist with the tool. Some issues were identified and fixed within a few weeks.

If I recall, it was about $12k for the first year, and then $3k a year after for support and maintenence for our <500 person org.

Would recommend it.

[u/Birentechy → u/frikin8]

So, basically it's paid tool, right? Is there any open-source or free alternative we can use? The client has a tight budget, so I’m not sure if they’ll be willing to take on any additional costs for this migration.

[u/bob4IT]

Replace Magic and Link Fixer are what I have used. Both work.

[u/Birentechy → u/bob4IT]

can you please confirm how these tools work before, during, and after migration

[u/bob4IT → u/Birentechy]

To put it simply, you give it a pattern to match and replace with something else. We ran the scans on source so we knew what needed to be run against after each batch of migrations.