Skip to content

Instantly share code, notes, and snippets.

@tamc tamc/macro.bas
Last active Aug 29, 2015

Embed
What would you like to do?
A macro to list all cells in a workbook that contain external references
Sub ShowAllLinksInfo() 'requires a worksheet to be added to the 'workbook and named LinksList
Dim aLinks As Variant
Dim i As Integer
Dim anyWS As Worksheet
Dim anyCell As Range
Dim reportWS As Worksheet
Dim nextReportRow As Long
Set reportWS = ThisWorkbook.Worksheets("LinksList")
reportWS.Cells.Clear
reportWS.Range("A1") = "Worksheet"
reportWS.Range("B1") = "Cell"
reportWS.Range("C1") = "Formula"
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then 'there are links somewhere in the workbook
For Each anyWS In ThisWorkbook.Worksheets
If anyWS.Name reportWS.Name Then
For Each anyCell In anyWS.UsedRange
If anyCell.HasFormula Then
If InStr(anyCell.Formula, ".xls") > 0 Then
nextReportRow = reportWS.Range("A" & Rows.Count).End(xlUp).Row + 1
reportWS.Range("A" & nextReportRow) = anyWS.Name
reportWS.Range("B" & nextReportRow) = anyCell.Address
reportWS.Range("C" & nextReportRow) = "'" & anyCell.Formula
End If
End If
Next ' end anyCell loop
End If
Next ' end anyWS loop
Else
MsgBox "No links to Excel worksheets detected."
End If 'housekeeping
Set reportWS = Nothing
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.