Excel Hyperlink Problem

[JoyP]

I have an Excel database file where applicable records are hyperlinked to the data files they describe; both spreadsheet and data files are stored on an internal network.
The hyperlinks were created by displaying the data location path in Firefox and copy pasting by folder, as the records were created. This gave the desired result of a display of short URLs in the spreadsheet.
I recently had to update the path in the hyperlinks because I had relocated the data files. I did this using a VB module and custom formula to display the path and then used an edit/replace routine to change the affected section.
This worked fine, except that it resulted in a display of the full URL path in the updated cells.
Is it possible to format these new hyperlinks to only show the short version again?

If you are able to suggest a solution - please be aware that I am only a dabbler and follower of step by step instructions rather than a developer, so idiot speak would be much appreciated

Many thanks to anyone for sharing expertise.

 

[Micron]

If the short form is gone, how would you get it back? You can loop over the hyperlinks and change the TextToDisplay property, but to what? This would add ... to the end (not what you want, I know) but it demonstrates that the display text can be altered.

Sub ShortVersion()
Dim i As Integer
Dim hLink As Hyperlink

For Each hLink In ActiveSheet.Hyperlinks
     hLink.TextToDisplay = hLink.TextToDisplay & "..."
Next

End Sub

 

[JoyP]

Thank you so much for trying to help Micron.
I've run out of time to work on this one but when I get back to it I will try this, maybe with some kind of generic text like "Open File" .