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 find all external links and broken links in workbook

Excel VBA Workbook LinkSources Method

LinkSources can be used to return an array of names of linked documents, editions, 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 path plus 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

Click here to see how to  find all external links and broken links in workbook.

Outbound References

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