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 want all the information of the external links and status used in formula, I highly recommend this VBA approach to you. In order to understand the VBA code, first you need to understand two Methods:

1. LinkSources
2. LinkInfo

LinkSources

Workbook Method LinkSources can be used to find all external links in a workbook, storing the data in any array.

Suppose you have two formula that link to another workbook, for example

='C:\Users\WYMAN\Desktop\folder\[FileB.xlsx]Sheet3'!$A$3
='C:\Users\WYMAN\Desktop\folder\[FileC.xlsx]Sheet3'!$A$3

The returned array is

Array (1) : C:\Users\WYMAN\Desktop\folder\FileB.xlsx
Array (2) : C:\Users\WYMAN\Desktop\folder\FileC.xlsx

Although LinkSources fails to locate the Cell address that contains the external link, we can loop through each used Range that contains formula and see if it contains the string of the link.

The next step is the add brackets [ ] to the workbook name.

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

Since external link can be with or without brackets, both of the below should be considered as as external links when you do the checking

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

LinkInfo

Workbook LinkInfo Method is to check status of linked workbook, so that we know whether the workbook link is broken or normal.

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.

Since LinkSources fails to identify which Cell is using the external link, the Macro has to loop through each used Range to see if the cell formula matches links in LinkSources. On the other hand, if a cell contains Named Range which uses external link, the Macro would loop through each underlying external link of all Named Range to find any match.

Sub listLinks()
    alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(alinks) Then
        Sheets.Add
        shtName = ActiveSheet.Name
        Set summaryWS = ActiveWorkbook.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 ActiveWorkbook.Worksheets
            If ws.Name <> summaryWS.Name Then
                For Each Rng In ws.UsedRange
                    If Rng.HasFormula Then
                        For j = LBound(alinks) To UBound(alinks)
                            filePath = alinks(j)   'LinkSrouces returns full file path with file name
                            Filename = Right(filePath, Len(filePath) - InStrRev(filePath, "\"))   'extract just the file name
                            filePath2 = Left(alinks(j), InStrRev(alinks(j), "\")) & "[" & Filename & "]"  'file path with brackets
                 
                            If InStr(Rng.Formula, filePath) Or InStr(Rng.Formula, filePath2) Then
                                nextrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row + 1
                                summaryWS.Range("A" & nextrow) = ws.Name
                                summaryWS.Range("B" & nextrow) = Replace(Rng.Address, "$", "")
                                summaryWS.Hyperlinks.Add Anchor:=summaryWS.Range("B" & nextrow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address
                                summaryWS.Range("C" & nextrow) = "'" & Rng.Formula
                                summaryWS.Range("D" & nextrow) = filePath
                                summaryWS.Range("E" & nextrow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus))
                                Exit For
                            End If
                        Next j
                            
                        For Each namedRng In Names
                            If InStr(Rng.Formula, namedRng.Name) Then
                                filePath = Replace(Split(Right(namedRng.RefersTo, Len(namedRng.RefersTo) - 2), "]")(0), "[", "") 'remove =' and range in the file path
                                nextrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row + 1
                                summaryWS.Range("A" & nextrow) = ws.Name
                                summaryWS.Range("B" & nextrow) = Replace(Rng.Address, "$", "")
                                summaryWS.Hyperlinks.Add Anchor:=summaryWS.Range("B" & nextrow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address
                                summaryWS.Range("C" & nextrow) = "'" & Rng.Formula
                                summaryWS.Range("D" & nextrow) = filePath
                                summaryWS.Range("E" & nextrow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus))
                                Exit For
                            End If
                        Next namedRng
                    End If
                Next Rng
            End If
        Next
        Columns("A:E").EntireColumn.AutoFit
        lastrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row
        
        For r = 2 To lastrow
            If ActiveSheet.Range("E" & r).Value = "File missing" Then
                countBroken = countBroken + 1
            End If
        Next
        
        If countBroken > 0 Then
            sInput = MsgBox("Do you want to remove broken links of status 'File missing'?", vbOKCancel + vbExclamation, "Warning")
            If sInput = vbOK Then
                For r = 2 To lastrow
                    If ActiveSheet.Range("E" & r).Value = "File missing" Then
                        Sheets(Range("A" & r).Value).Range(Range("B" & r).Value).ClearContents
                        dummy = MsgBox(countBroken & " broken links removed", vbInformation)
                    End If
                Next
            End If
        End If
    Else
        MsgBox "No external links"
    End If
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. Column B contains a hyperlink that goes to the Cell that contains the link.

 

If the status in column E is “File missing”, a message box pops up and then you can click on OK to clear the Cell value.