Q: How can I get the URL of a hyperlink field?

[Darryl Friesen]

Is there some function that will return the URL/destination of a hyperlinked
cell? I've been given a spreadsheet with a few thousand hyperlink fields and
I need to extract them.

"Save As..." appears to be broken, in that it only saves the text of the
hyperlinked cell, not the URL (making it totally useless). I've tried saving
in different formats (CSV, tab and space delimited etc) but nothing works.

My thought then was to use a formula to extract the URL portion of the
hyperlink into another cell, then doing the export/save as, but I can't seem
to make that work either. I'd prefer NOT to use the Save as HTML option (CSV
is so much more convenient!!), although it looks as if that's the only way.

I'd appreciate any help or suggestions.

Thanks,

- Darryl

----------------------------------------------------------------------
Darryl Friesen, B.Sc., Programmer/Analyst Darryl....@usask.ca
Consulting & Development http://gollum.usask.ca/
Department of Computing Services 163 Murray Building
University of Saskatchewan Main Library
----------------------------------------------------------------------
"The Truth Is Out There"

[John Walkenbach]

I don't have an answer for your "save as" problem.

You can copy the VBA function listed below to a module:

Function URL(cell)
URL = cell.Hyperlinks(1).Address
End Function

Then, you can write a formula such as:

=URL(A1)

This will return the URL for the hyperlink in cell A1.

----- Posted by John Walkenbach of JWalk & Associates -----
----- Visit "The Spreadsheet Page" -----
----- http://www.j-walk.com/ss -----

Darryl Friesen wrote in message
<#AZirPaY#GA....@uppssnewspub04.moswest.msn.net>...

[George Nicholson]

Darryl:

See if this helps. Just change the Sheet1 reference to whatever you need it
to be and the Sheet2 reference to point to a blank sheet.
*********************
Sub ListHyperlinks()
' For each hyperlink on Sheet 1, will create list on Sheet 2
' showing the cell address of the hyperlink in Column A
' and the hyperlink reference in Column B.
Dim h As Hyperlink
Dim x As Integer

x = 1
For Each h In Worksheets("Sheet1").Hyperlinks
Worksheets("Sheet2").Range("A" & x).Value = h.Range.Address
Worksheets("Sheet2").Range("B" & x).Value = h.Name
x = x + 1
Next
End Sub
************************
Good Luck,
George