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 |
