Search for external links using VBA

[svjensen]

I need to search for external links in a workbook using VBA.

I was thinking of doing it along the lines of:

If Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
    SearchFormat:=False).Activate Then
 
    MsgBox "External Link found!"
 
Else
    MsgBox "No external links found!"
End if

This works if an external link exists, but if no links are found I get the following error message:
Run-time error '91':
Object variable or With block variable not set

How can I achieve what I want?

/Soren

 

[Richard Schollar]

How does that search string (What:="") identify an external link?

 

[svjensen]

It should of course be (What:="[").
Sorry!

 

[Richard Schollar]

Ah I see - I wondered if this was a technique I'd never seen before!

You can assign the Find result to a rnage variable (which won't error if nothing is found) and test this instead:

Dim rngExtLink As Range
 
Set rngExtLink = Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
    SearchFormat:=False)
 
If Not rngExtLink Is Nothing Then
 
    MsgBox "External Link found!"
 
Else
    MsgBox "No external links found!"
End if

 

[svjensen]

Beautiful! Thank you

/Soren

 

[Weaver]

Just because there's always more than 1 way..

If hasLinks Then
 
    MsgBox "External Link found!"
Else
    MsgBox "No external links found!"
End if

Here's the function

Function hasLinks()
    hasLinks = False
    On Error Resume Next
    hasLinks = (LBound(ActiveWorkbook.LinkSources(xlExcelLinks)) >= 0)
End Function

This might be quicker than performing a find on a particularly data heavy sheet. Of course, it won't tell you where the links are...

 

[svjensen]

Weaver, thanks for following up.

Both concepts work fine for me, as I only need to know if external links exist.

One follow up though. Both concepts will catch external links in formulas (and possible others as well).
Is there a way to catch external links in NamedRanges?

/Soren