0

I have a spreadsheet which contain several hyperlinks, some of which are from shapes. I am trying to loop through them, to see where each of them point in order to later remove some of them. For the hyperlinks contained in cells the following loop has worked:

Sub a()
  Dim ws As Worksheet, hl As Hyperlink, o As Shape

  For Each ws In Worksheets
    For Each hl In ws.Hyperlinks
      Debug.Print hl.Address
    Next
  Next
End Sub

But that seems to skip all the hyperlinks originating from shapes or other objects.

Is there any way I can loop through those as well? I have tried stuff like:

Sub a()
  Dim ws As Worksheet, hl As Hyperlink, o As Shape


  For Each ws In Worksheets
    For Each o In ws.Shapes
      For Each hl In o.Hyperlinks
        Debug.Print hl.Address
      Next
    Next
  Next
End Sub

But that gives me a runtime error 91 on the debug.print line. Googling gives me nothing. So, have any of you got an idea of how to print the addresses?

Community
  • 1
  • 1
eirikdaude
  • 3,106
  • 6
  • 25
  • 50

1 Answers1

3

A Shape doesn't have a .Hyperlinks property, only a .Hyperlink one and you'll get an error from it if there is no associated hyperlink, so you need an error handler. For example:

        On Error Resume Next
        Set hl = o.Hyperlink
        On Error GoTo 0
        If Not hl Is Nothing Then 
            Debug.Print hl.Address
            set hl = Nothing
        End If
Rory
  • 32,730
  • 5
  • 32
  • 35
  • Ah, I see. I did try with `o.HyperLink.Address` as well, but got an error then too. I should have figured it would do that if it couldn't find a hyperlink. Having tried your solution though, it seems that there are errant hyperlinks in other objects than shapes too. Would you by any chance know which objects can contain them? – eirikdaude Mar 10 '15 at 11:12
  • *Anything* in the drawing layer of the sheet is a shape - what's the issue? – Rory Mar 10 '15 at 11:17
  • There are some broken links in the sheet, but I can't figure out where they are located (apparently not in `Worksheets.Hyperlinks` or `Shape.Hyperlink`). The reason I want to know what the parent object of the links are, is that I *may* want to remove the parent object, depending a bit on what it is. Was just using `Hyperlink.Address` to determine if I was able to locate the objects or not. – eirikdaude Mar 10 '15 at 12:54
  • If you know there are broken links, you must know where they are, no? How else do you know they exist? – Rory Mar 10 '15 at 14:32
  • That's not a hyperlink, it's another kind of link - possibly a formula or data query. – Rory Mar 10 '15 at 15:33
  • That would explain why I can't find it using the methods described above :-P I suppose the proper way to search for such a link is a bit much to go into here in the comments? – eirikdaude Mar 10 '15 at 15:50
  • Probably. You could start by checking the Connections on the Data tab. – Rory Mar 10 '15 at 16:36
  • Yeah, I have checked both the Connections window, which doesn't show the links, and the Edit links window, which show that there are links, but not where they are located. In the end, I found [this code](http://www.vbaexpress.com/kb/getarticle.php?kb_id=1064), which revealed that the links are connected to a chart in the workbook: http://imgur.com/HVwqXRm http://imgur.com/xjdQ9om – eirikdaude Mar 11 '15 at 10:08