software

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.

If there is a hyperlink in C5, then the UDF
=hyp(quot;C5quot;) will return the URLHere is the UDF:

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function
--
Gary's Studentquot;polytxquot; wrote:

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

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 gt; 0 Then _
HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

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

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

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

quot;Gary''s Studentquot; gt; wrote in message
...
gt; If there is a hyperlink in C5, then the UDF
gt; =hyp(quot;C5quot;) will return the URL
gt;
gt;
gt; Here is the UDF:
gt;
gt; Function hyp(r As String) As String
gt; hyp = Range(r).Hyperlinks(1).Address
gt; End Function
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;polytxquot; wrote:
gt;
gt; gt; How do I retrieve the url portion of a cell that has a hyperlink stored int
gt; gt; it. The value function only returns the readable text.
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!

quot;David McRitchiequot; wrote:

gt; Hi Polb and Gary's,
gt;
gt; If there is no hyperlink I think you would get zero
gt;
gt; try
gt;
gt; Function HyperlinkAddress(cell) As String
gt; If cell.Hyperlinks.Count gt; 0 Then _
gt; HyperlinkAddress = cell.Hyperlinks(1).Address
gt; End Function
gt;
gt; to use:
gt; =personal.xls!hyperlinkaddress(A1)
gt;
gt; more informiation an variations see
gt; www.mvps.org/dmcritchie/excel...perlinkaddress
gt;
gt; ---
gt; HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt;
gt; quot;Gary''s Studentquot; gt; wrote in message
gt; ...
gt; gt; If there is a hyperlink in C5, then the UDF
gt; gt; =hyp(quot;C5quot;) will return the URL
gt; gt;
gt; gt;
gt; gt; Here is the UDF:
gt; gt;
gt; gt; Function hyp(r As String) As String
gt; gt; hyp = Range(r).Hyperlinks(1).Address
gt; gt; End Function
gt; gt; --
gt; gt; Gary's Student
gt; gt;
gt; gt;
gt; gt; quot;polytxquot; wrote:
gt; gt;
gt; gt; gt; How do I retrieve the url portion of a cell that has a hyperlink stored int
gt; gt; gt; it. The value function only returns the readable text.
gt;
gt;
gt;

There is a reference on the referred to page that would point you to
www.mvps.org/dmcritchie/excel....htm#havemacro
this reference is more specific as you have the User Defined Function
and just want to install it. Once you've installed a UDF or a macro
it will go a lot easier the second time.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;FrozenRopequot; gt; wrote in message ...
gt; I feel like a dork, but I'm not familiar with how to create a UDF or how to
gt; work with personal.xls. Is there a primer that you can direct me to that can
gt; help?
gt;
gt; I have a 2,000 row spreadsheet where I need to extract the URL an embedded
gt; column, and can't stand the thought of doing that by hand. ;o)
gt;
gt; Thanks in advance for baby-sitting me on this!
gt;
gt; quot;David McRitchiequot; wrote:
gt;
gt; gt; Hi Polb and Gary's,
gt; gt;
gt; gt; If there is no hyperlink I think you would get zero
gt; gt;
gt; gt; try
gt; gt;
gt; gt; Function HyperlinkAddress(cell) As String
gt; gt; If cell.Hyperlinks.Count gt; 0 Then _
gt; gt; HyperlinkAddress = cell.Hyperlinks(1).Address
gt; gt; End Function
gt; gt;
gt; gt; to use:
gt; gt; =personal.xls!hyperlinkaddress(A1)
gt; gt;
gt; gt; more informiation an variations see
gt; gt; www.mvps.org/dmcritchie/excel...perlinkaddress
gt; gt;
gt; gt; ---
gt; gt; HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt; gt;
gt; gt; quot;Gary''s Studentquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; If there is a hyperlink in C5, then the UDF
gt; gt; gt; =hyp(quot;C5quot;) will return the URL
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Here is the UDF:
gt; gt; gt;
gt; gt; gt; Function hyp(r As String) As String
gt; gt; gt; hyp = Range(r).Hyperlinks(1).Address
gt; gt; gt; End Function
gt; gt; gt; --
gt; gt; gt; Gary's Student
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;polytxquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; How do I retrieve the url portion of a cell that has a hyperlink stored int
gt; gt; gt; gt; it. The value function only returns the readable text.
gt; gt;
gt; gt;
gt; gt;
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 quot;C:\My Documents\MyFile.ext FileRefquot; where 'FileRef'
is a bookmark or a worksheet amp; 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, quot;/quot;, quot;\quot;)
If Trim(HAddr) = quot;quot; Then HAddr = ThisWorkbook.FullName
If InStr(HAddr, quot;..\quot;) Then HAddr = ThisWorkbook.Path amp; Replace(HAddr,
quot;..\quot;, quot;quot;)
If InStr(HAddr, quot;:quot;) = False Then HAddr = ThisWorkbook.Path amp; quot;\quot; amp; HAddr
HSubAddr = Source.Hyperlinks(1).SubAddress
If HSubAddr = quot;quot; Then
HLinkAddr = HAddr
Else
HLinkAddr = HAddr amp; quot;: quot; amp; HSubAddr
End If
End Function

Sub TestLink()
Set Source = Range(quot;A1quot;)
Called = True
HLinkAddr Source
If Dir(HAddr, vbNormal) = quot;quot; Then
MsgBox quot;The hyperlink source file: quot; amp; vbCrLf amp; HAddr amp; vbCrLf amp; quot;does
not existquot;
Else
MsgBox quot;The hyperlink source file: quot; amp; vbCrLf amp; HAddr amp; vbCrLf amp;
quot;exists.quot;
End If
Called = False
End Sub

Cheersquot;FrozenRopequot; gt; wrote in message
...
gt; I feel like a dork, but I'm not familiar with how to create a UDF or how
to
gt; work with personal.xls. Is there a primer that you can direct me to that
can
gt; help?
gt;
gt; I have a 2,000 row spreadsheet where I need to extract the URL an embedded
gt; column, and can't stand the thought of doing that by hand. ;o)
gt;
gt; Thanks in advance for baby-sitting me on this!
gt;
gt; quot;David McRitchiequot; wrote:
gt;
gt; gt; Hi Polb and Gary's,
gt; gt;
gt; gt; If there is no hyperlink I think you would get zero
gt; gt;
gt; gt; try
gt; gt;
gt; gt; Function HyperlinkAddress(cell) As String
gt; gt; If cell.Hyperlinks.Count gt; 0 Then _
gt; gt; HyperlinkAddress = cell.Hyperlinks(1).Address
gt; gt; End Function
gt; gt;
gt; gt; to use:
gt; gt; =personal.xls!hyperlinkaddress(A1)
gt; gt;
gt; gt; more informiation an variations see
gt; gt; www.mvps.org/dmcritchie/excel...perlinkaddress
gt; gt;
gt; gt; ---
gt; gt; HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov.
2001]
gt; gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt; gt;
gt; gt; quot;Gary''s Studentquot; gt; wrote in
message
gt; gt; ...
gt; gt; gt; If there is a hyperlink in C5, then the UDF
gt; gt; gt; =hyp(quot;C5quot;) will return the URL
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Here is the UDF:
gt; gt; gt;
gt; gt; gt; Function hyp(r As String) As String
gt; gt; gt; hyp = Range(r).Hyperlinks(1).Address
gt; gt; gt; End Function
gt; gt; gt; --
gt; gt; gt; Gary's Student
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;polytxquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; How do I retrieve the url portion of a cell that has a hyperlink
stored int
gt; gt; gt; gt; it. The value function only returns the readable text.
gt; gt;
gt; gt;
gt; gt;
How do we enter UDF (user defined function) into EXCEL and which menu do we
need to click?

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Functionquot;Gary''s Studentquot; wrote:

gt; If there is a hyperlink in C5, then the UDF
gt; =hyp(quot;C5quot;) will return the URL
gt;
gt;
gt; Here is the UDF:
gt;
gt; Function hyp(r As String) As String
gt; hyp = Range(r).Hyperlinks(1).Address
gt; End Function
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;polytxquot; wrote:
gt;
gt; gt; How do I retrieve the url portion of a cell that has a hyperlink stored int
gt; gt; it. The value function only returns the readable text.

www.mvps.org/dmcritchie/excel/install.htm

-- Regards,Peo Sjoblom
quot;Tommyquot; gt; wrote in message
...
gt; How do we enter UDF (user defined function) into EXCEL and which menu do
gt; we
gt; need to click?
gt;
gt; Function hyp(r As String) As String
gt; hyp = Range(r).Hyperlinks(1).Address
gt; End Function
gt;
gt;
gt; quot;Gary''s Studentquot; wrote:
gt;
gt;gt; If there is a hyperlink in C5, then the UDF
gt;gt; =hyp(quot;C5quot;) will return the URL
gt;gt;
gt;gt;
gt;gt; Here is the UDF:
gt;gt;
gt;gt; Function hyp(r As String) As String
gt;gt; hyp = Range(r).Hyperlinks(1).Address
gt;gt; End Function
gt;gt; --
gt;gt; Gary's Student
gt;gt;
gt;gt;
gt;gt; quot;polytxquot; wrote:
gt;gt;
gt;gt; gt; How do I retrieve the url portion of a cell that has a hyperlink stored
gt;gt; gt; int
gt;gt; gt; it. The value function only returns the readable text.