Converting Hyperlinks to function based hyperlinks

[Erimikos]

I was hoping I could get some assistance here.

As a heads-up, I am terrible at VB, and have never even looked at Excel macro's before today.

I have a user who uses a large spreadsheet with links to documents all over my organizations share drive. Periodically, he will leave the system up overnight with the sheet open, and after patches are installed, the system will convert all the links to instead of searching the UNC path looking in c:\users\%username%\AppData\Roaming\.... where the links do not exist. I understand this is Microsoft trying to maintain the links through offline files, but not an option here.

I use shadow copies to pull a backup of the file, but all the links now point to folders within the shadow copy, instead of current data.

With a bunch of google searching I have been able to create a macro to fix that problem for all the links and change the text to the correct address.

However, what I am now trying to do is prevent this from happening in the first place by converting the cells to formula based links.

ie.. change a cell with a text field of 'IA0087' and a URL of \\Server\Share\TMB\Bldg Folders\IA0087 TMB-"
to instead contain
=HYPERLINK("\\Server\Share\TMB\TMB Bldg Folders\IA0087 TMB-", "IA0087")

I understand this should prevent Excel from automatically updating the cell...

I have figured out that I can use this to enter the formula into a cell:

ActiveCell.FormulaR1C1 = "=HYPERLINK(""\\Server\Share\TMB\TMB Bldg Folders\IA0087 TMB-"", ""IA0087"")"

But I am not sure how to create a loop that will go through all the cells on a sheet, or even a selection at a time, and convert the current hlink.address to text filling the target portion of the formula, and then to convert the current hlink.TextToDisplay to the friendly name in the hyperlink field..

Complicating things further, not all of the folders have the same string after the building number..

All are under \\Server\Share\TMB\TMB Bldg Folders\, but some have "TMB-", some have, "TMB", some have "TMB None on File"

So.. to make a long story short, HALP!

If anyone could point me in the right direction or give me some tips I would be extremely grateful.

If anyone was interested, or curious, the script I used from a web-page and modified in order to fix the problem of the links pointing to previous version was

Sub FixLinks()

Dim intStart As Integer
Dim intEnd As Integer
Dim strCol As String
Dim hLink As Hyperlink

intStart = 3
intEnd = 400

For i = intStart To intEnd

For Each hLink In ActiveSheet.Hyperlinks
'hLink.TextToDisplay = Replace(hLink.TextToDisplay, "Share\@GMT-2016.05.05-12.06.14\TMB\", "Share\TMB\")
hLink.Address = Replace(hLink.Address, "Share\@GMT-2016.05.05-12.06.14\TMB\", "Share\TMB\")
Next hLink

Next i

End Sub