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.

asktoupdatelinks

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

broken_links

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

broken_links_02

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

broken_links_04

Select Inquire > Press OK

broken_links_05

Now you have a new tab INQUIRE

broken_links_06

Workbook Relationship shows how your workbook is connected to other workbooks

broken_links_07

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

broken_links_10

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.

broken_links_08

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.

external_links_11

 

13 thoughts on “Excel find all external links and broken links in workbook

  1. Hello Wyman, I tried above code in one of my excel file. Which shows that The excel file have 45 links status as “File missing”. I doubted and again downloaded DigDb addin and tried Digdb in same excel file and guess what it only found 37 bad links which is having File Missing Status.
    Still I would like to thank you that its and awesome job. and hoping that you will make the changes which is necessary.

    Thanks & regards,
    Ashutosh Pandey

  2. Hi Ashutoh, thanks for your feedback. I am happy to investigate if you can provide me the sample workbook and highlight which are incorrectly marked as File missing. I reviewed the code but I can’t see anything wrong. my email:terethan@hotmail.com

  3. Nice tutorial, thanks. Another insidious place links can lurk: defined names. I scratched my head until I saw more links in the name manger.

  4. Hi Wyman
    Nice one – saved me a lot of time. Thanks. Some remarks though.
    – if the referenced workbook is opened in the same instance of excel then the formulas are changed and no longer contain the FILEPATH, but only the FILENAME (so searching the filepath will find nothing)
    – other places that can hide external links: FORM CONTROLS on a sheet (e.g. a Button) can have Macros assigned from another Workbook (and will only work if it is opened);
    – also CHARTS, I believe, can have an external data source.

    1. Hi bobsan, thanks for pointing out the limitation of my Macro, this is helpful for the future enhancement

  5. Hi, I just copied the macro in a module and it doesn’t even take off – red font throughout, Compile error: Sub or Function not defined. Any ideas? Working in Excel 2016
    Thanks in advance!

    1. I think you haven’t copied the whole code, check if you have copied
      Public Function linkStatusDescr(statusCode)

  6. Thanks for the great tips and this page is really useful- understanding this more.

    I was wondering- is there any way we can filter a vba search to only search for the ‘broken’ external links..rather than all external links?

    1. You may add the below procedure in the end
      Public Sub filterErrors()
      lastrow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row

      For r = lastrow To 2 Step -1
      If ActiveSheet.Range(“E” & r).Value = “No errors” Then
      ActiveSheet.Rows(r).Delete
      End If
      Next
      End Sub

Leave a Reply

Your email address will not be published.