I need to change many hyperlinks on an excel spreadsheet to point to a new location. I want to keep the filename the same, just change the network path. I've tried a few different scripts with no success.
This doesn't work:
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String
Set wks = ActiveSheet
sOld = "\\servera\2014\DC1\CDP\Archive_Target\Denise's Files\Residential Work Orders\"
sNew = "\\NEWSERVER\SharedData\Location\Residential Maintenance\Denise's Files\Residential Work Orders\"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)