ExcelBanter - Hyperlinks property return a relative path ...

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Hyperlinks property return a relative path ... (https://www.excelbanter.com/excel-programming/393714-hyperlinks-property-return-relative-path.html)

Hyperlinks property return a relative path ...

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

Hyperlinks property return a relative path ...

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

Garette wrote:

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

--

Dave Peterson

Hyperlinks property return a relative path ...

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

Hyperlinks property return a relative path ...

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???

Garette wrote:

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

--

Dave Peterson