VBA Script for Automatic Excel Link Update

Issue : 
We migrated our file server. End users started complaining that hyperlinks are not working in excel file. 

Observation : When excel link gets created, it does not store Drive name. It either stores Computer Name/IP Address or relative path of the folder.

If you have migrated file server with different IP Address and Computer name, then Hyperlinks which are having old entries will not work.

Solution : 
Here is a small VB Script which you can configure in your PERSONAL.XLSB Macro and save your valuable time.

'*******************************************
Sub Correct_Hyperlink()
    Dim wks As Worksheet
    Dim hl As HyperLink
    Dim sOld As String
    Dim sNew As String
    Dim ChangeCount As Integer
    ChangeCount = 0
    Dim Change As Integer
    Change = 0
    Set wks = ActiveSheet
    sOld = "\\172.241.17.234\"
    sNew = "\\172.241.17.123\"
    For Each hl In wks.Hyperlinks
   
        If InStr(hl.Address, sOld) > 0 Then
            ChangeCount = ChangeCount + 1
            Change = 1
        End If
       
        hl.Address = Replace(hl.Address, sOld, sNew)

    Next hl
   
If Change = 1 Then
    MsgBox "Macro Updated " & ChangeCount & " Links"
Else
    MsgBox "All links are already updated or old links are not present"
End If

End Sub

'*******************************************