Updating URL Hyperlinks within Excel

By | November 7, 2013

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.

Developer Ribbon

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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *