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

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

Why you should NOT remove .NET Framework 4.5 in Windows 2012 R2

Dear All,

I am writing this post after lot of struggling to find out the root cause of Issue which I faced when we removed .NET Framework 4.5 from Windows Server 2012 R2.

Action taken before issue : Uninstalled .NET Framework 4.5





Issue reported : 
- GUI got uninstalled. MSTSC / Remote was loading only command prompt.
No any GUI options were available. 
- Powershell was not available



Resolution : 

Thankfully there is a revert back process for this.
Run below command ONE BY ONE (It will take lot of time. Dont worry event if it dosent show any progress on Screen. Grab some Coffee and have 10 Min break). 

DISM.exe /online /enable-feature /all /featurename:NetFx4
 
DISM.exe /online /enable-feature /all /featurename:MicrosoftWindowsPowerShell

Goto Powershell by Typing "PowerShell" at Command Prompt.

Install-WindowsFeature Server-Gui-Shell, Server-Gui-Mgmt-Infra

Restart-Computer 



Finally you got your beautiful GUI Back. Congrats!

Do comment if it helped you. Cheers!!!