Visual Basic Code to Find and Replace Multiple Hyperlinks

Tonight, with the help of Copilot, I was able to generate the below vba code which will go through an Excel workbook, finding and replacing multiple static hyperlinks (not entered with an active formula) with new hyperlinks.

The code is set to work on both links to network filepaths, and to web-based urls. However filepaths which reference a network drive letter, should be converted to full UNC file paths, which include the server name. So instead of "P:\Acme\Trial\Closing.pptx", the path should be entered as "\\dewey.local\chi07\AL\Usershare"

[If you need help finding the full UNC file path for files on your network enter the command, 'net use' in command prompt and it will generate a list of the server paths for each drive you're mapped to.

Adding find and replace pairs to the vba code is easy. The old path is simply preceded by 'linkMap.Add' and then the two paths are enclosed in quotes and separated with a comma.

I tested the code tonight with a few hundred urls and the workbook was updated almost instantly.

Sub ReplaceNetworkDriveHyperlinks()

Dim ws As Worksheet

Dim hl As Hyperlink

Dim linkMap As Object

Dim oldLink As Variant

' Create a dictionary to store old and new network path mappings

Set linkMap = CreateObject("Scripting.Dictionary")

' Add your old and new UNC path pairs here

linkMap.Add "\\Server1\Shared\Docs\Report1.pdf", "\\Server2\Archive\Docs\Report1.pdf"

linkMap.Add "\\Server1\Shared\Docs\Report2.pdf", "\\Server2\Archive\Docs\Report2.pdf"

linkMap.Add "\\Server1\Shared\Images\", "\\Server2\Archive\Images\"

' Loop through all worksheets

For Each ws In ThisWorkbook.Worksheets

' Loop through all hyperlinks in the worksheet

For Each hl In ws.Hyperlinks

For Each oldLink In linkMap.Keys

If InStr(1, hl.Address, oldLink, vbTextCompare) > 0 Then

' Replace the old part of the path with the new one

hl.Address = Replace(hl.Address, oldLink, linkMap(oldLink))

hl.TextToDisplay = Replace(hl.TextToDisplay, oldLink, linkMap(oldLink)) ' Optional

End If

Next oldLink

Next hl

Next ws

MsgBox "Network hyperlinks updated successfully!", vbInformation

End Sub

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer. All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information. This policy is subject to change at any time.  The owner is not an attorney, and nothing posted on this site should be construed as legal advice.   Litigation Support Tip of the Night does not provide confirmation that any e-discovery technique or conduct is compliant with legal, regulatory, contractual or ethical requirements.