Microsoft updates their software on a regular basis, which is a good thing. But, it creates havoc with relative hyperlinks that I have in any open spreadsheets. After the update, it points my hyperlinks to a new location where the update process saved my spreadsheet and my relative hyperlinks then point to "never, never land".
It used to drive me crazy. I have summary spreadsheet of all topside metrics on my securities holdings. Certain fields for each security are hyperlinked to more detailed information on the holding, such as a myriad of fundamental metrics for 20 years of data. It would take hours to correct this issue by going into each hyperlink and "repointing" the hyperlinks to the correct location.
To solve this troublesome issue, I created a simple macro in VBA to "repoint" the hyperlinks correctly. It now takes about 2 seconds to correct the issue. I sure like the time savings!
Many times in my career, I've seen people do long and arduous processes in Excel when the same process could be easily handled with VBA. They usually say, "I don't have time to learn VBA.". My response is ALWAYS, "You don't have time NOT to learn VBA!
Be smart, be well-read and be successful.
Here is the code:
Sub FixHyperLinkAddresses()
'
' Corrects hyperlinks that have been changed by Microsoft's update process
'
Dim wsht As Worksheet
Dim hLink As Hyperlink
Dim lngCnt As Long
'Set a counter so the user knows how many have been fixed
'when the process is complete
lngCnt = 0
'Process every worksheet
For Each wsht In Worksheets
'Process every hyperlink in each spreadsheet
For Each hLink In wsht.Hyperlinks
'Creates a list of all hyperlinks in the spreadsheet in the
'intermediate window, just in case you want to review the
'hyperlinks it identified
Debug.Print hLink.Address
'Now replace any hyperlinks having the first address
'"../../../../../Users/markm/AppData/StockEvaluations" in this case
'with this "..\..\..\StockEvaluations"
hLink.Address = Replace(hLink.Address, _
"../../../../../Users/markm/AppData/StockEvaluations", _
"..\..\..\StockEvaluations")
'Add it to the count of hyperlinks
lngCnt = lngCnt + 1
'Get the next hyperlink
Next hLink
'Get the next worksheet
Next wsht
'Let the user know that it is done and the number
'of hyperlinks identified.
MsgBox "Done " & lngCnt
End Sub
