Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Extracting URLs from Hyperlinked Images

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Extracting URLs from Hyperlinked Images.

One way you can use data from the Internet in an Excel worksheet is to copy it from a Web page and then paste it into the worksheet. For instance, you can select a table of data on a Web page, press Ctrl+C to copy it to the Clipboard, select a cell in Excel, and then press Ctrl+V. Excel does its best to parse the data and put it in the proper cells, just like it was in the original table.

The problem is that you'll often get more than just the table data. If there were other objects in the data you copied from the Web, those objects are also pasted into the worksheet. It is not uncommon to end up with all sorts of small graphics in the worksheet. If these graphics were originally hyperlinks, you may want to actually extract the hyperlink and then delete the graphic. This would make the data in the worksheet much more usable.

The way to do this is with a macro. Once you've pasted the Web information into the worksheet, run the following macro.

Sub ConvertHLShapes()
    Dim shp As Shape
    Dim sTemp As String

    For Each shp In ActiveSheet.Shapes
        sTemp = ""
        On Error Resume Next 'go to next shape if no hyperlink
        sTemp = shp.Hyperlink.Address
        On Error GoTo 0
        If sTemp <> "" Then
            shp.TopLeftCell.Value = sTemp
            shp.Delete
        End If
    Next
End Sub

This macro steps through each of the shapes in the worksheet. It then checks to see if the shape has an associated hyperlink. If it does, then the address of that hyperlink (in the sTemp variable) is placed into the cell at the top-left corner of where the shape is located. The macro deletes any shapes that have hyperlinks; you can force it to delete all shapes in the worksheet by simply moving the shp.Delete line to the outside of the If ... End If structure.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2907) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Extracting URLs from Hyperlinked Images.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Primož Majhen    17 Mar 2016, 05:00
Awesome macro!
Thanks!
Steve Hunt    24 Dec 2015, 03:57
I really appreciate your work! Thank you! You certainly don't get this from the book! You've saved me a countless number of hours. Much appreciated!
Masud Rana Rajshahi    16 Jan 2015, 19:44
Very useful tips. It's saved many hours of my life :)

Thanks a lot!!!
Multivac42    14 Jan 2015, 10:59
This is extremely useful. You just saved me lots of hours!

Thank you so much!
Patricia C    08 Aug 2014, 13:16
Thank you so much for this Tips.
It was so-o-o Extremely Helpful.
Muhammad    30 Jul 2014, 09:59
Thanks alot ...............really appreciated
Alban    17 Jul 2014, 09:39
Merci beaucoup. Très simple, beaucoup de temps gagné. Thank you very much.
Stephen    15 Mar 2014, 23:36
Thanks Allen, really useful code.
Cheers
Stephen
NZ
Scott    07 Feb 2014, 09:48
You've just saved me hours. If there's such a thing as good karma you've certainly earned a lot!
Thanks so much!
Jim Bannon    28 Oct 2013, 20:18
Allen,

This is exactly what I needed. I am trying to export fans from facebook.
I can copy them onto an Excel spreadsheet, but the Hyperlinks are hidden under the pictures.

However, I am an absolute Newbie with Macro programming. It would have helped if there was a Macro file I could have downloaded and run.

I searched on YouTube and as I type this I am discovering ALT+F11 will open a VBA editor. This may be the first step?

Any way you can "package this solution" for a newbie?

Thanx,
Jim Bannon
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.