Excel VBA Workbook LinkSources Method to find external links

This Excel tutorial explains how to use Workbook LinksSources Method in Excel VBA to find all external links.

You may also want to read:

How to refresh all external data of closed workbook

Excel VBA Workbook LinkSources Method

LinkSources can be used to return an array of names of linked documents, editions, or DDE or OLE servers.

Assume that 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

Note that only the file name is returned, not the actual Cell address.

Syntax of Workbook LinkSources

Workbook.LinkSources(Type)

Name	Required/Optional	Description
Type	Optional	One of the constants of XlLink which specifies the type of link to return.Return all types if Type is omitted. Name Value Description xlExcelLinks 1 The link is to an Excel worksheet. xlOLELinks 2 The link is to an OLE source. xlPublishers 5 Macintosh only. xlSubscribers 6 Macintosh only.

Example of Workbook LinkSources

The below code creates a new worksheet and list all external source name (workbook name).

Sub listLinks()
    Dim aLinks As Variant
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        Sheets.Add
        For i = 1 To UBound(aLinks)
            Cells(i, 1).Value = aLinks(i)
        Next i
    End If
End Sub

The below code update all links in workbook

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Click here to see how to refresh external links of closed workbook.

Excel find all external links and broken links in workbook

The below code shows how to find all external links and broken links in workbook with status on each linked source.

Sub listLinks2()
    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
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

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff821922.aspx

 

 

 

Excel VBA Workbook LinkSources Method to find external links