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:
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.
