Excel Hyperlink Problem

JoyP

New Member
Joined
Feb 16, 2023
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
VBA Code:
Sub ShortVersion()
Dim i As Integer
Dim hLink As Hyperlink

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

End Sub
 
Upvote 0
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" . :)
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top