This Excel tutorial explains how to use Workbook LinksSources Method in Excel VBA to find all external links.
You may also want to read:
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
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
|Type||Optional||One of the constants of XlLink which specifies the type of link to return.Return all types if Type is omitted.
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
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.