External links in Excel 1

[jerstep]

How can I get a list of all the links to external data sources in my Excel workbook ? Menus or VBA, it doesn't matter)

Thanks.
J.

 

[xlbo]

Try this - stolen from excel L archives - tested and it seems to work - creates a new worksheet and lists the links

Sub ListLinks()
Dim x, s, r, a, c
Dim External, UserChoice
Let x = 1
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Formulas&quot.Delete
Application.DisplayAlerts = True
External = MsgBox(Prompt:="List external links only?", _
Title:="External or Ext & Internal", _
Buttons:=vbYesNoCancel)
Select Case External
Case vbCancel
Exit Sub
Case vbYes
UserChoice = "\["
Case Else
UserChoice = "!"
End Select
On Error GoTo 0
Worksheets.Add Before:=Sheets(1)
ActiveSheet.Name = "Formulas"

Cells(1, 1).Formula = "Sheet Ref"
Cells(1, 2).Formula = "Cell Ref"
Cells(1, 3).Formula = "Formula"
With Range("1:1&quot
.Font.Bold = True
.Font.ColorIndex = 5
.Font.Size = 14
.Font.Underline = True
End With
Range("B:B&quot.ColumnWidth = 10
Range("C:C&quot.ColumnWidth = 52
Range("A2&quot.Select
ActiveWindow.FreezePanes = True
Application.ScreenUpdating = False
For Each s In ActiveWorkbook.Worksheets
Set r = Nothing
On Error Resume Next
Set r = s.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0
If Not r Is Nothing Then
For Each a In r.Areas
For Each c In a.Cells
If InStr(c.Formula, UserChoice) Then
x = x + 1
Worksheets("Formulas&quot.Cells(x, 1) = s.Name
Worksheets("Formulas&quot.Cells(x, 2) = _
c.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)
Worksheets("Formulas&quot.Cells(x, 3) = "'" & c.Formula
End If
Next c
Next a
End If
Next s
Range("A1&quot.Select
Columns("A:A&quot.ColumnWidth = 18.78
ActiveWindow.Zoom = 85
Application.ScreenUpdating = True
If ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.ExclusiveAccess
End If
End Sub

HTH
Geoff

 

[jerstep]

Thanks Jeoff!