Repairing links in Excel documents

Another off-topic post today, because lately it’s all been migrations – Exchange migrations, data migrations, printer migrations…. I think I might have to rename this blog Miss Migrations!

As part of an enourmous data migration involving server name and drive letter changes, I was tasked with repairing links in thousands of Excel spreadsheets. We trialled a commercial link fixing product but abandonned it because of its unfriendly habit of making you start right back from the beginning whenever it crashed, which it did regularly.

I wrote a few vbscripts and, while definitely slower and not without their own problems (mostly caused by the Excel docs themselves, and their multiplicitous configurations, macros and protective devices), I could at least control my file lists, and make modifications as needed.

The scripts

The following scripts can be used to first search for, and then repair links in Excel spreadsheets. The links can be either source links (where source data is imported from another file – see Edit-Links in Excel) or hyperlinks in spreadsheet cells.

These scripts are not fast. However you can run multiple instances at a time so, if you have a lot to get through, break it down into subfolders, rather than hitting a whole file system at once.

Script Description
find_links_folder.vbs Search a folder and subfolders for Excel documents with sourcelinks and hyperlinks
find_links_filelist.vbs Search a list of Excel documents for sourcelinks and hyperlinks
change_sourcelinks.vbs Change sourcelinks in a list of Excel documents using string replace
change_hyperlinks.vbs Change hyperlinks in a list of Excel documents using string replace