Hyperlinks property return a relative path ...

  • Thread starter Thread starter Garette
  • Start date Start date
G

Garette

Hi,

I use Excel 2002 and I have a problem with the Hyperlinks vba property.
I have created some links in my sheet like C:\Folder\test.xls (using
Insert/Hyperlink menu)
When I save my sheet, the link becomes ..\..\..\Folder\test.xls (relative
path)

What I want to do, is to recover the full absolute path using a macro like :

Sub Test()
For Each h In Cells.Hyperlinks
ren = h.Address
MsgBox ren
Next
End Sub

But this macro only give a relative path.
When I move my mouse cursor on the hyperlink, the full path appears in a
comment.
It seems that the absolute path is stored somewhere, bur where ?

Does somebody know how to recover the full absolute path using vba ?
Thanks for your help
 
Maybe something like:

Option Explicit
Sub Test()
Dim hyper As Hyperlink
For Each hyper In ActiveSheet.Hyperlinks
hyper.Address = hyper.TextToDisplay
Next hyper
End Sub

You may be able to avoid this problem by:

File|Properties|Summary Tab|Hyperlink Base
change it to C:\
(something that's always available)

=======
Personally, I try to use the =hyperlink() worksheet function. I think that they
behave much nicer.

=hyperlink("http"//" & a2, "Click me")

or

=hyperlink("file:////" & $a$1 & a2, "Click me")
if $a$1 contained the path: \\server\share\folder\folder\
and a2 contained the name of the file: myFile.doc
 
Hi Dave,

Thanks for your answers.

Concerning the Sub Test, it doesn't Work because the TextToDisplay is
relative too :(

Concerning the Properties solution, it seems to be really a very good
solution !
Instead of C:\ I tried to put a space and it gives the full path from C:\
(when I tried with C:\ the path start after "C:\")

Concerning the Hyperlink worksheet function, I will propose to users to use
it.

Thank you very much
Regards
 
Is there anything that kept the old hyperlink address (maybe when you hovered
over it)? Maybe you can use that property in the sub.

I don't understand the comment about using c:\ and what changed--the properties
screen or the hyperlink URL???
 
Back
Top