Hyperlinks & filename

  • Thread starter Thread starter Elin
  • Start date Start date
E

Elin

Hi!

If I got a hyperlink can I, by using a formula, get the
filename of the hyperlink?

Kindly regards
//Elin
 
Elin,

Simply use the address property of the hyperlink, e.g.

MsgBox Range("A1").Hyperlinks(1).Address
 
How? I'm sorry but I don't understand.
//Elin
-----Original Message-----
Elin,

Simply use the address property of the hyperlink, e.g.

MsgBox Range("A1").Hyperlinks(1).Address

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks





.
Click to expand...
 
Elin,

Sorry, what I assumed you would know how to use it. I don't know of a
builtin formula, but you can construct your own. For instance

Function HyperlinkAddress(rng As Range)
If rng.Count > 1 Then
HyperlinkAddress = CVErr(xlErrValue)
Else
HyperlinkAddress = rng.Hyperlinks(1).Address
End If
End Function

Put this in a code module and you can use it in a worksheet circa

=HyperlinkAddress(A1)
 
Hi Elin,
If you have a lot of hyperlinks you want to reveal such as
pasting your favorites into a worksheet, you will quickly see
that not every cell you look at has a hyperlink, and rather than
showing a zero you would probably want to show null. This is
one I did...

Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

You can find others that are more complete depending
on what you actually have or want at
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url
particularly Bill Manville's which includes Excel links as well.
 
Thanks, It worked just as I wanted... =)
//Elin
-----Original Message-----
Elin,

Sorry, what I assumed you would know how to use it. I don't know of a
builtin formula, but you can construct your own. For instance

Function HyperlinkAddress(rng As Range)
If rng.Count > 1 Then
HyperlinkAddress = CVErr(xlErrValue)
Else
HyperlinkAddress = rng.Hyperlinks(1).Address
End If
End Function

Put this in a code module and you can use it in a worksheet circa

=HyperlinkAddress(A1)


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks





.
Click to expand...
 
Back
Top