Extract URLs

[Thomas Guignard]

Hi there

I need to extract URLs from cells that are in the form

=LIEN_HYPERTEXTE("http://www.example.com";"URL")

(french version of Excel, I guess you can work out the english equivalent)

These cell appear as hyperlinks, but I need to extract the actual URL
and store it in another column.

I have found this URL-extract function somewhere on the web (can't find
the reference right now):

Public Function ShowAddress(rng As Range) As String
If rng.Cells.Count > 1 Then
ShowAddress = CVErr(xlErrValue)
Else
ShowAddress = rng.Hyperlinks.Item(1).Address

End If
End Function

But if I try to use this function in my spreadsheet, like
=ShowAddress(E2) for example, I get a #VALUE! error. And indeed, in
debug mode I see that the value of the variable rng in the above
function is set to "URL" so the hyperlink text, not the whole cell
content. Which explains why the function can't extract hyperlink
information.

How can I solve my problem? How can I make sure I get the actual cell
content and not only the result of the LIEN_HYPERTEXTE function?

Thanks for your help

 

[Harlan Grove]

I need to extract URLs from cells that are in the form

=LIEN_HYPERTEXTE("http://www.example.com";"URL")

(french version of Excel, I guess you can work out the english
equivalent)

Sure, and my response works in English, so you can work out the
necessary changes for French versions.

These cell appear as hyperlinks, but I need to extract the actual
URL and store it in another column.

....

While cells in which you enter simple HYPERLINK function calls have
effective hyperlinks, those hyperlinks are NOT part of the cell's
Hyperlinks property. Only hyperlinks you add to cells using the menu
command Insert > Hyperlink... are added to the cell's Hyperlinks
property. Annoying, but that's just how Excel 'works'.

You have to parse cell formulas to extract urls from HYPERLINK
function calls. Something like


Function foo(r As Range) As String
Dim k As Long, q As Boolean

If r.Hyperlinks.Count > 0 Then
foo = r.Hyperlinks(1).Address

ElseIf r.Formula Like "=HYPERLINK(*)" Then 'modify as needed
foo = r.Formula
foo = Mid(foo, 12, Len(foo) - 12)
k = Len(foo)
q = (Mid(foo, k, 1) = """")
Do While k > 0
If Mid(foo, k, 1) = "," And _
(q Xor Mid(foo, k + 1, 1) <> """") Then Exit Do
k = k - 1
Loop
foo = Evaluate(Left(foo, k - 1))

End If

End Function

 

[Thomas Guignard]

Thanks Harlan for your answer.

Actually, I solved my problem in pretty much the same way, albeit less
elegantly than you did:

Public Function ShowAddress(rng As Range) As String
Dim form, startpoint, endpoint
If rng.Cells.Count > 1 Then
ShowAddress = CVErr(xlErrValue)
Else
form = rng.Formula
startpoint = InStr(form, "http://")
form = Right(form, Len(form) - startpoint + 1)
endpoint = InStr(form, """")
form = Left(form, endpoint - 1)
ShowAddress = form
End If
End Function

But it beats me why VBA doesn't provides a way to access a formula's
parameters (or variables, or whatever they're called). It would have
been so easy with a rng.Formula.Variables(1)...

Thanks again