Removing or correcting hyperlinks with VBA

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:

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
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.

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 &
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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.