Tell if an excel file has external links

Topic

New Reply

Hi All!

I’m looking for a way (in VBA) of telling if an excel file has links to other spreadsheets. Basically, I want to put this into a vba function and then do something like …

Code:

if doesThisFileHaveExternalLinks then
    msgbox "external links detected ... this is bad"
endif

Oh – while I am here – I guess I will shortly be looking for a way of listing these external links .

Ruff

Reply | Quote

Replies

XL2003 has a Link Sources method that returns an array of links in a workbook.
I haven’t tested it, but I suspect it may not find every type of link possible.
For something quick and dirty you can do a UsedRange search for a bracket… “[”
‘–
Jim Cone
Portland, Oregon
XL add-ins

Reply | Quote

This is what I ended up using

Code:

Public Function isHaveLinkedCells() As Boolean
Dim lCell As Variant
Dim lCells As Range
Dim lSheet As Object
Dim lLinkTest As Integer

' remember the current location
' StoreCurrent   0 Then
                isHaveLinkedCells = True
                GoTo TheEnd
            End If
        Next
    End If
Next

TheEnd:
'ReturnToCurrent  ' you can guess what this does
On Error GoTo 0
End Function

Reply | Quote

Suggest you change…
lLinkTest = InStr(1, lCell.Value, “.xls]”)

To…
lLinkTest = InStr(1, lCell.Formula, “.xls]”)

Also that check will always return 0 for a xl2007 workbook extension.
You might want to just check for both a bracket and an exclamation point.

Your check will be quick but not complete as you can also have links in charts, shapes, hyperlinks, names, pivot tables and ?
‘–
Jim Cone
Portland, Oregon USA
( Special Sort add-in )

Reply | Quote

lLinkTest = InStr(1, lCell.Formula, “.xls]”)

Yeah – sloppy testing on my behalf. Just found out the above fix during a live demo … sigh.

Also that check will always return 0 for a xl2007 workbook extension.
You might want to just check for both a bracket and an exclamation point.

Your check will be quick but not complete as you can also have links in charts, shapes, hyperlinks, names, pivot tables and ?
‘–
Jim Cone
Portland, Oregon USA
( Special Sort add-in )

correct – but good enough for what I am after as I am only interested in links in cells.

Thanks for your comments.

Reply | Quote