How to get the URL of a hyperlink in a cell?

[Ads Killer]

Hi,

I am new to Excel 97 and I apologise if this question had already been
asked.
I would like to know whether there is a worksheet or visual basic function
that can
get the URL of a hyperlink in a cell.

For example, cell A1 contains a hyperlink of
"http://www.somehost.com/somepages.htm" . I want to get the URL of
cell A1 and put it in cell B1, so cell B1 will contain the value
"http://www.somehost.com/somepages.htm" (without link, plain text only). Is
this something like B1 = url(A1) ...?

Also, can I use Excel to check broken link? For example, to check if the web
page mentioned in cell B1 ("http://www.somehost.com/somepages.htm" ) returns
an
Error 404 not found.

Thanks for any help.

[Ogilvy, Thomas, W., Mr., ODCSLOG]

To get the link, you can use the vba construct:

range("C2").Hyperlinks(1).Address

You could put this in a userdefined function

Public Function URL(rng as Range)
URL = rng.Hyperlinks(1).Address
End Function

There is no built in way to have Excel check the link (as far as I
know). I would assume you could create a VBA program that could go down
a list of links, open them in Excel and then scan the page for the 404
or perhaps operate IE using OLE automation. I haven't tried it.

Regards,
Tom Ogilvy

[Chip Pearson]

Tom's reply is correct if you used the "Insert Hyperlink" tool. It
won't work, though, if you are using the =HYPERLINK() worksheet
function. In that case, use

TheAddress = Mid(Range("A1").Formula, 13, InStr(1,
Range("A1").Formula, ",") - 14)

to get the address of the hyperlink.

Ogilvy, Thomas, W., Mr., ODCSLOG <OGIL...@hqda.army.mil> wrote in
message
news:278EF0D03897D111880...@dadc020.hqda.pentagon.mil.
..