Recently I had an issue where a user moved a spreadsheet that contained hundreds of hyperlinks to files stored on the server. Obviously once moved none of the links worked and I was tasked with trying to change all these links without spending weeks doing it manually.
The only real option is to create a macro that will run through all the links comparing part of the link and changing it, for example.
The link to the old file was \\server01\folder1\file2 it now need to be \\server01\folder2\folder2\file2.
Start by saving the excel spreadsheet as a macro enabled document, File-Save As and save it as xlsm
Next add the developer ribbon to excel. To do this simple Right click the ribbon, customise ribbon and then tick Developer.
Now in the developer ribbon click Macros and create a new macro.
Edit the macro Visual Basic should pop up.
now use the code below
Sub FixHyperlinks() Dim wks As Worksheet Dim hl As Hyperlink Dim sOld As String Dim sNew As String Set wks = ActiveSheet sOld = "\\server01\folder1" sNew = "\\server01\folder2\folder2" For Each hl In wks.Hyperlinks hl.Address = Replace(hl.Address, sOld, sNew) Next hl End Sub
Now if you run the macro or “Step into” (F8) you will notice the macro checks every link in the “ActiveSheet” and if it finds the old text \\server01\folder1 it will replace it with \\server01\folder2\folder2. This means that every file link with the location \\server01\folder1 will be changed to the new location.
You can have multiple
sOld = "\\server01\folder1" sNew = "\\server01\folder2\folder2" For Each hl In wks.Hyperlinks hl.Address = Replace(hl.Address, sOld, sNew) Next hl
if you require different location changes.
One more tip, if you have problems running the macro try using step into instead to see where it falls over.