hyperlinks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brian.freed@globalproductsinc.com - 06 Dec 2007 22:47 GMT

number one when my users do insert hyperlink and browse to a network
drive using excel outlook etc... it puts the unc path in to the server
where the file is located is there away to make it use the drive
letter in all cases? Also is there an easy way to chang the existing
links over after the fact maybe to use the hyperlink function in excel
or whatever. reason to use the drive letter is there is a replicated
site (our second office) with its own server where the files are
stored that is going to have huge challenges very shortly if i can't
do this. Thanks in advance for any and all help!!!

Reply to this Message

Gary''s Student - 07 Dec 2007 11:08 GMT

This may help for "easy" hyperlinks ( without SubAddresses )

Three steps:

1. Convert all the Inserted hyperlinks into hyperlink formulas with the
following macro:

Sub hyper_converter()
Dim h As Hyperlink
dq = Chr(34)
beginning = "=hyperlink(" & dq
middle = dq & "," & dq
ending = dq & ")"
For Each h In ActiveSheet.Hyperlinks
s1 = h.Address
s2 = h.TextToDisplay
Set r = h.Range
r.Clear
r.Formula = beginning & s1 & middle & s2 & ending
Next
End Sub

2. Using Find/Replace force any url's in the formulas back to the mapped
drive letters.

3. Test the result.

Signature

Gary''s Student - gsnu2007b

> number one when my users do insert hyperlink and browse to a network
> drive using excel outlook etc... it puts the unc path in to the server
[quoted text clipped - 5 lines]
> stored that is going to have huge challenges very shortly if i can't
> do this. Thanks in advance for any and all help!!!

Reply to this Message