By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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
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
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 &
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