extract XL cell's hyperlink 1

[BigInch]

Looking for a way to extract the URL from an XL cell (cell is typically displaying a link as an underlined name).

Thanks.



BigInch-born in the trenches.

http://virtualpipeline.spaces.msn.com

 

[IRstuff]

Excel's spreadsheet functions don't appear to support finding hyperlinks, but Visual Basic does recognize a hyperlink.

Something like this:
For Each h In Worksheets(1).Hyperlinks
test = h.Address
Next


TTFN

 

[BigInch]

Xllent! Thanks IRStuff.

BigInch-born in the trenches.

http://virtualpipeline.spaces.msn.com

 

[joerd]

It works on a range argument, too:

Function GetHyperlink(R As Range) As Variant
    GetHyperlink = R.Hyperlinks(1).Address
End Function

can be called as [tt]=GetHyperlink(A1)[/tt] (for example), which gives you the hyperlink address in cell A1.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.