Hi FrozenRope,

Here's a UDF to extract the Hyperlink from a cell, plus a macro that uses
the same UDF to test whether a file referenced by a hyperlink exists.

You can use the UDF just like a formula. The syntax is:
=HLinkAddr(A1)
where A1 is the cell you want to extract the link from. The UDF returns the
Hyperlink in the form "C:\My Documents\MyFile.ext FileRef" where 'FileRef'
is a bookmark or a worksheet & cell reference.

Dim Source As Range
Dim Called As Boolean
Dim HAddr As String
Dim HSubAddr As String

Public Function HLinkAddr(Source As Range)
If Called = True And Source.Hyperlinks.Count = 0 Then Exit Function
HAddr = Replace(Source.Hyperlinks(1).Address, "/", "\")
If Trim(HAddr) = "" Then HAddr = ThisWorkbook.FullName
If InStr(HAddr, "..\") Then HAddr = ThisWorkbook.Path & Replace(HAddr,
"..\", "")
If InStr(HAddr, ":") = False Then HAddr = ThisWorkbook.Path & "\" & HAddr
HSubAddr = Source.Hyperlinks(1).SubAddress
If HSubAddr = "" Then
HLinkAddr = HAddr
Else
HLinkAddr = HAddr & ": " & HSubAddr
End If
End Function

Sub TestLink()
Set Source = Range("A1")
Called = True
HLinkAddr Source
If Dir(HAddr, vbNormal) = "" Then
MsgBox "The hyperlink source file: " & vbCrLf & HAddr & vbCrLf & "does
not exist"
Else
MsgBox "The hyperlink source file: " & vbCrLf & HAddr & vbCrLf &
"exists."
End If
Called = False
End Sub

Cheers


"FrozenRope" wrote in message
...
I feel like a dork, but I'm not familiar with how to create a UDF or how
to
work with personal.xls. Is there a primer that you can direct me to that
can
help?

I have a 2,000 row spreadsheet where I need to extract the URL an embedded
column, and can't stand the thought of doing that by hand. ;o)

Thanks in advance for baby-sitting me on this!

"David McRitchie" wrote:

Hi Polb and Gary's,

If there is no hyperlink I think you would get zero

try

Function HyperlinkAddress(cell) As String
If cell.Hyperlinks.Count 0 Then _
HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

to use:
=personal.xls!hyperlinkaddress(A1)

more informiation an variations see
http://www.mvps.org/dmcritchie/excel...perlinkaddress

---
HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov.
2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Gary''s Student" wrote in
message
...
If there is a hyperlink in C5, then the UDF
=hyp("C5") will return the URL


Here is the UDF:

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function
--
Gary's Student


"polytx" wrote:

How do I retrieve the url portion of a cell that has a hyperlink
stored int
it. The value function only returns the readable text.