Excel Hyperlink Object Address Property only shows relative path

[dashbored]

My God, I can't take it. I have a column of cells with hyperlinks to different files. In my VBA code I want to get the absolute address of the hyperlink so that I can pass it to an Access table. I can find no property in the hyperlink object that allows me to see this (the address property is a relative path). But when I wave my cursor over each cell, I can see the full path. Can anyone tell me how I can do this??? A secondary question, how can I grab the contents of the note that pops up when I pass my cursor over a cell. Thank you so much for any help you can give. I've been Googling my brains out and find nothing. I just can't believe nobody has had this problem before.

 

[PHV]

A starting point:
With ActiveCell.Hyperlinks(1)
MsgBox "Name=" & .Name & vbCrLf & "Address=" & .Address
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dashbored]

Thanks for the feedback! No I still get this relative path for the address property:
"../Test area/Feedback/Input/filename.doc"

But the full path is contained in the note that pops up when I move my cursor over that cell:
"file:///c:\documents and settings\j5555\My Documents\Test Area\Feedback\Input\filename.doc"

I need the full path to pass to Access so I can create a hyperlink in the table I'm populating. I can't believe that this is a problem. Any ideas?

 

[macropod]

Hi,

AFAIK, you'll get the full path only if the source file is in a different folder. If there's the possibility that the source file is in the same folder, then all you need is to get the target workbook's path. On that basis, you could try something like:

Sub Test()
With ActiveCell.Hyperlinks(1)
If InStr(1, .Address, ":") = False Then
HAddr = ThisWorkbook.Path
Else
HAddr = .Address
End If
MsgBox "Name=" & .Name & vbCrLf & "Address=" & HAddr
End With
End Sub

The above code assumes that, if there's no drive separator ) in the filename, the source & target are in the same folder.

Cheers

 

[dashbored]

Thank you! What an obvious work around. Sometimes I get so wrapped up in trying to find the straight-forward way to get something done in VBA that I get frustrated and lose my ability to adapt.

Actually, the source and target are not in the same folder, and yet it still gives me a relative path, but no matter. I can get the full path like this:

For Each hlk In ThisWorkbook.Sheets(1).Hyperlinks
HAddr = Replace(hlk.Address, "/", "\")
If InStr(HAddr, "..\") Then _
HAddr = ThisWorkbook.Path & _
Mid(HAddr, InStr(HAddr, "..\") + 2)
msgbox Haddr
Next hlk

What an utterly cheezemo way to get the full path! How can there not be a simpler way to get it? Anyway, at least this works.

Thanks again to you both!

 

[eman6]

Sorry to comment here after such a long time of this post.
I have come across this while I was looking for a solution to a similar problem.
It might be possible to add a function in an adjacent column in Excel to convert the hyperlink to the full path and then read this full path from VBA.

Anybody can advise how to convert (extract?) a hyperlink to the fullpath within Excel?

Thank you.


______________________________________
Eman2005
Technical User