Editing hyperlinks en masse
Harlan Grove
>Hello out there....I'm having a bear of a time editing a
>table with a bunch of hyperlinks. Example file is
>attached. If anyone can help out a newbie here, it would
>be much appreciated.
I don't open files. Most others (the sensible ones) won't either.
Explain the problem in plain text.
Joe Colletti
either.
>
>Explain the problem in plain text.
OK.
I copied a table from webpage that contains many
hyperlinks. These links would be very useful, but they
were are all created as "relative" links, referring to
pages in relation to their own position instead of an
absolute. Example: /co/capsule/7/0,2163,41317,00.html
Since all of these pages are located at www.hoovers.com,
so adding this as a string to the front of each of these
links should yield absolute links that function as
desired. Unfortunately, I'm stuck because I don't know
where the actual hyperlink string lives in the
spreadsheet, so I'm not able to build a Concatenate
formula that references it.
Any ideas? Much thanks! Joe
Harlan Grove
...
>I copied a table from webpage that contains many
>hyperlinks. These links would be very useful, but they
>were are all created as "relative" links, referring to
>pages in relation to their own position instead of an
>absolute. Example: /co/capsule/7/0,2163,41317,00.html
>
>Since all of these pages are located at www.hoovers.com,
>so adding this as a string to the front of each of these
>links should yield absolute links that function as
>desired. Unfortunately, I'm stuck because I don't know
>where the actual hyperlink string lives in the
>spreadsheet, so I'm not able to build a Concatenate
>formula that references it.
The easiest way to do this if *all* hyperlinks point to the same web site is
to use File > Properties to bring up the workbook's Properties dialog,
select the Summary tab, then enter (in this case) http://www.hoovers.com in
the Hyperlink base field and clock OK. Do this then try out your hyperlinks.
If you have hyperlinks from multiple sites, you're going to need VBA. In its
infinite wisdom, Microsoft doesn't provide a worksheet function to access
the Address property of hyperlinks (at least not in the version I'm using).
That means VBA (or some other scripting language capable of Automation) is
the only way to access these Address properties. Here's a udf to do so.
Function geturl(r As Range) As String
Dim hb As String, ha As String, ps As String
hb = r.Parent.Parent.BuiltinDocumentProperties("Hyperlink Base")
ha = r.Areas(1).Cells(1, 1).Hyperlinks(1).Address
If Right(hb, 1) = "/" Then hb = Left(hb, Len(hb) - 1)
If Left(ha, 7) = "http://" _
Or Left(ha, 6) = "ftp://" _
Or left(ha, 7) = "mailto:" _
Or Left(ha, 2) = "\\" _
Or Left(ha, 3) Like "[A-Za-z]:\" Then
geturl = ha
Else
ps = IIf(hb Like "*://*", "/", "\")
If Right(hb, 1) <> ps Then hb = hb & ps
If Left(ha, 1) = ps Then ha = Mid(ha, 2)
geturl = hb & ha
End If
End Function
Use it in worksheet formulas like =geturl(A1)