Excel find all external links and broken links in workbook

This Excel tutorial explains how to find all external links and broken links in workbook using Find and Replace, Relationship Diagram, Macro.

You may also want to read

Excel VBA Workbook LinkSources Method

Excel VBA refresh closed workbook

Excel find all external links and broken links in workbook

There are several ways to find external links and broken links in workbook, some are easy to use but have limitations, I will talk about each method in this article. Note that this article writes about file link, not Hyperlink.

Basically there are three cases you would use external links:

1) In a formula, directly retrieve linked value

2) In a formula, retrieve value of Named Range defined in this workbook (simply go to Formulas > Name Manager to check)

3) In a formula, retrieve value of Named Range defined in external workbook

Less common use of external links include Objects and graphs. I am not going to talk about these less popular ones in this tutorial, you can click here to find out more from Microsoft support.

Use Find and Replace to find all external links

Normally, if you link a workbook to external source, you will see something like this in formula

='C:\Users\WYMAN\Desktop\folder\[FileB.xlsx]Sheet1'!$A$1

External links always refer to a another file name within square brackets [ ], we can make use of this characteristics and search the any string within workbook that contains [ or ], but the assumption is that you don’t actually have a text that contains square brackets.

Although you may think this is a ridiculous method, it is suggested by Microsoft. In fact, Excel maintains a dependency table for the links instead of just looking for the square brackets.

Press CTRL+F > input and select details as follows > Find all

Now you can see all external links in the result box.

This method cannot search Named Range defined in another workbook because [ ] are not present in the formula.

This method also fails to indicate broken links.

Use Cell Relationship Diagram to find all external links and broken links in workbook

Excel 2013 introduces a new add-in to view the relationship diagram. To activate the add-in, navigate to Files > Options > Add-Ins

In dropdown box, select COM Add-ins > Press Go

Select Inquire > Press OK

Now you have a new tab INQUIRE

Workbook Relationship shows how your workbook is connected to other workbooks

If workbook link is broken, you will find the Excel logo turns red

Worksheet Relationship shows how your worksheets are connect to other worksheets, it can also show the linked workbook information.

Similar to Workbook Relationship, you will also see the Excel logo turns red if the link is broken.

Cell Relationship is relatively useless for our topic because it can only find the linked references for a single Cell each time.

Use Excel VBA to find all external links in workbook

If you only want to look for links used in formula, I highly recommend this VBA approach to you.

Excel has a workbook Method called LinkSources, which can be used to find the external workbook path+name.

For example,

C:\Users\WYMAN\Desktop\folder\FileB.xlsx

Although it fails to locate the Cell address that contains this path, we can loop through each used Range to find which one contains formula with this path to determine if the Cell contains a link.

As mentioned above, the external links can have square brackets or without square brackets, my Sub Procedures will take both into consideration in searching.

For example,

C:\Users\WYMAN\Desktop\folder\FileB.xlsx and C:\Users\WYMAN\Desktop\folder\[FileB.xlsx]

Use Excel VBA to find broken links

We can use Workbook LinkInfo Method to check status of each workbook.

Syntax of LinkInfo

Workbook.LinkInfo(Name, LinkInfo, Type, EditionRef)

Name	Required/Optional	Data Type	Description
Name	Required	String	The name of the link.
LinkInfo	Required	XlLinkInfo	The type of information to be returned. Name Value Description xlEditionDate 2 Applies only to editions in the Macintosh operating system. xlLinkInfoStatus 3 Returns the link status. xlUpdateState 1 Specifies whether the link updates automatically or manually.
Type	Optional	Variant	One of the constants of XlLinkInfoType specifying the type of link to return. Name Value Description xlLinkInfoOLELinks 2 OLE or DDE server xlLinkInfoPublishers 5 Publisher xlLinkInfoSubscribers 6 Subscriber
EditionRef	Optional	Variant	If the link is an edition, this argument specifies the edition reference as a string in R1C1 style. This argument is required if there’s more than one publisher or subscriber with the same name in the workbook.

LinkInfo Method returns a status as below.

XlLinkStatus	Description
xlLinkStatusCopiedValues	Copied values.
xlLinkStatusIndeterminate	Unable to determine status.
xlLinkStatusInvalidName	Invalid name.
xlLinkStatusMissingFile	File missing.
xlLinkStatusMissingSheet	Sheet missing.
xlLinkStatusNotStarted	Not started.
xlLinkStatusOK	No errors.
xlLinkStatusOld	Status may be out of date.
xlLinkStatusSourceNotCalculated	Not yet calculated.
xlLinkStatusSourceNotOpen	Not open.
xlLinkStatusSourceOpen	Source document is open.

Because the Method returns a number instead of XlLinkStatus, I need to create another Function linkStatusDescr to convert the XlLinkStatus to Description.

VBA code – find all external links and broken links in workbook

The below Procedure makes use of both LinkSources and LinkInfo Methods to find all external links and broken links in workbook.

Sub listLinks()
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        Sheets.Add
        shtName = ActiveSheet.Name
        Set summaryWS = ThisWorkbook.Worksheets(shtName)
        summaryWS.Range("A1") = "Worksheet"
        summaryWS.Range("B1") = "Cell"
        summaryWS.Range("C1") = "Formula"
        summaryWS.Range("D1") = "Workbook"
        summaryWS.Range("E1") = "Link Status"
        For Each Ws In ThisWorkbook.Worksheets
            If Ws.Name <> summaryWS.Name Then
                For Each rng In Ws.UsedRange
                    If rng.HasFormula Then
                        For j = LBound(aLinks) To UBound(aLinks)
                            linkStr = Left(aLinks(j), InStrRev(aLinks(j), "\")) & "[" & Right(aLinks(j), Len(aLinks(j)) - InStrRev(aLinks(j), "\"))
                            If InStr(rng.Formula, linkStr) Or InStr(rng.Formula, aLinks(j)) Then
                                lastRow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row
                                summaryWS.Range("A" & lastRow + 1) = Ws.Name
                                summaryWS.Range("B" & lastRow + 1) = rng.Address
                                summaryWS.Range("C" & lastRow + 1) = "'" & rng.Formula
                                summaryWS.Range("D" & lastRow + 1) = aLinks(j)
                                summaryWS.Range("E" & lastRow + 1) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(aLinks(j)), xlLinkInfoStatus))
                            End If
                        Next j
                    End If
                Next rng
            End If
        Next
    Else
        MsgBox "No external links"
    End If
    Columns("A:E").EntireColumn.AutoFit
End Sub

Public Function linkStatusDescr(statusCode)
           Select Case statusCode
                Case xlLinkStatusCopiedValues
                    linkStatusDescr = "Copied values"
                Case xlLinkStatusIndeterminate
                    linkStatusDescr = "Unable to determine status"
                Case xlLinkStatusInvalidName
                    linkStatusDescr = "Invalid name"
                Case xlLinkStatusMissingFile
                    linkStatusDescr = "File missing"
                Case xlLinkStatusMissingSheet
                    linkStatusDescr = "Sheet missing"
                Case xlLinkStatusNotStarted
                    linkStatusDescr = "Not started"
                Case xlLinkStatusOK
                    linkStatusDescr = "No errors"
                Case xlLinkStatusOld
                    linkStatusDescr = "Status may be out of date"
                Case xlLinkStatusSourceNotCalculated
                    linkStatusDescr = "Source not calculated yet"
                Case xlLinkStatusSourceNotOpen
                    linkStatusDescr = "Source not open"
                Case xlLinkStatusSourceOpen
                    linkStatusDescr = "Source open"
                Case Else
                    linkStatusDescr = "Unknown status"
            End Select
End Function

Result

All external links and status are displayed in a new worksheet.

Note that only Cell formula are checked, not Object or Named Range created in this workbook.

 

Excel find all external links and broken links in workbook