ARCHIVE: TIPS & TRICKS
Removing or correcting hyperlinks with VBA
Eric Mantion
09.05.2000
Rating: -4.55- (out of 5)
Removing or correcting hyperlinks with VBA
Reader Eric M. Mantion sent this week's tip that concerns making sure
that the hyperlinks in a Web page always point to the place you want,
not necessarily to their original destination.
Removing or Correcting Hyperlinks with VBA
Here's the situation: You've just copied a large grid of data from a
web page into Excel, but the hyperlinks were also transferred. The
problem is the hyperlinks are "relative"; they all reference a
subdirectory of the web page (like /datasheets/SomeSheet.htm) and do
not include the leading part of the URL (like www.SomeWebPage.com).
You've got 3 choices:
1. Manually modify each of the 1200 cells you just copied using
+k (insert hyperlink)
2. Delete all the hyperlinks with a 5 line Macro.
3. Fix all the hyperlinks with a slightly longer Macro.
Here's how to do the second two:
** DELETE HYPERLINKS **
1. Select the cells in question. It doesn't matter how many -- it
could be 20, or 20,000.
2. Enter this simple macro into the worksheet, or better yet, a
special workbook that holds your most often used macros. I call
mine "CommonMacros.xls."
0/00Public Sub RemoveHyperlinks()
For Each Cell In Selection
Cell.Hyperlinks.Delete
Next Cell
End Sub
3. Run the macro and watch the hyperlinks disappear.
Note: It doesn't matter of some of the cells have hyperlinks and some
don't, it will only affect the ones with hyperlinks and leave the rest
alone.
** CORRECT THE HYPERLINKS **
1. Again, select the cells in question.
2. Use this macro:
0/00Public Sub ModifyHyperlinks()
Dim Prefix, Msg, Style, Title
0/00
Msg = "What do you want to add in front of the URL?"
Title = "Leading URL"
Prefix = InputBox(Msg, Title)
For Each Cell In Selection
If Cell.Hyperlinks.Count <> 0 Then
Cell.Hyperlinks.Item(1).Address = Prefix &
Cell.Hyperlinks.Item(1).Address
End If
Next Cell
End Sub
3. Run the macro and all of the hyperlinks should go from:
"/datasheets/SomeSheet.htm" to
"www.SomeWebPage.com/datasheets/SomeSheet.htm" if you entered
"www.SomeWebPage.com" as the Prefix.
Note: Again, it doesn't matter of some of the cells have hyperlinks and
some don't. The macro will only affect the ones with hyperlinks and
leave the rest alone. But if you run this twice, the hyperlink would
be: "www.SomeWebPage.comwww.SomeWebPage.com/datasheets/SomeSheet.htm"
which would obviously be an error so BE CAREFUL!
-------------------------------------------------------------
Eric M. Mantion is currently working as a Product Marketing Engineer
for a Semiconductor Manufacture, but has begun picking up contract
programming jobs on the side. If you have suggestions, he can be
reached at Emantion@aol.com
|
|
|
|
|
|
