I use this macro to find the cell addresses that are linked to external
workbooks as well as the external workbooks path. I added a column to show
what the linked formula is (don't know if you'll need that or not).
If you run this on the active workbook it'll create a list of your links.
Sub FindLink()
Dim Report As Object
Set Report = Sheets.Add
Dim LinkList As Variant
Dim LinkPath As Variant
On Error Resume Next
LinkList = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(LinkList) Then
For i = 1 To UBound(LinkList)
LinkPath = Split(LinkList(i), "\", -1, vbTextCompare)
For Each x In Worksheets
If x.Name < Report.Name Then
For Each y In x.UsedRange
If InStr(1, y.Formula, LinkPath(UBound(LinkPath)), vbTextCompare) 0
Then
Report.Select
ActiveCell.Value = x.Name & y.Address
ActiveCell.Offset(0, 1).Value = LinkList(i)
ActiveCell.Offset(0, 2).Value = Replace(y.Formula, _
"=", "", 1, 1, vbTextCompare)
ActiveCell.Offset(1, 0).Select
End If
Next y
End If
Next x
Next i
End If
End Sub
"Brookfield" wrote:
a new software is being implemented however all of the files will change and
not have a space in the name. this will break all of my links. how can I
know what the links were from each file. is there a macro that I can run?
|