Removing or correcting hyperlinks with VBA

Reader Eric M. Mantion explains how to ensure hyperlinks always point to the place you want, not their original destination.


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

This was first published in September 2000

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close