A macro to list all cells in a workbook that contain external references

macro.bas

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