top of page

hunting external links


Tonight I used the below vba code posted here by Domenic to successfully generate a list of each cell in a workbook which contained a link to an outside Excel file.


This code is more convenient than searching for .xl in Find with the 'Look in: Formulas' setting.


Keep in mind that if you open an Excel workbook and this troubling warning appears:




. . . the source of the problem will not be shown in the first column of the 'Find All' search results. Look in the reference column (or the Formula column in the Find tool) for a reference to a different file embedded in a formula. See: prices.xlsx in this example for a search run in the inventory.xlsx workbook:





Option Explicit

Sub ListLinks()

    Dim Wks             As Worksheet
    Dim rFormulas       As Range
    Dim rCell           As Range
    Dim aLinks()        As String
    Dim Cnt             As Long

    If ActiveWorkbook Is Nothing Then Exit Sub
    
    Cnt = 0
    For Each Wks In Worksheets
        On Error Resume Next
        Set rFormulas = Wks.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not rFormulas Is Nothing Then
            For Each rCell In rFormulas
                If InStr(1, rCell.Formula, "[") > 0 Then
                    Cnt = Cnt + 1
                    ReDim Preserve aLinks(1 To 2, 1 To Cnt)
                    aLinks(1, Cnt) = rCell.Address(, , , True)
                    aLinks(2, Cnt) = "'" & rCell.Formula
                End If
            Next rCell
        End If
    Next Wks
    
    If Cnt > 0 Then
        Worksheets.Add before:=Worksheets(1)
        Range("A1").Resize(, 2).Value = Array("Location", "Reference")
        Range("A2").Resize(UBound(aLinks, 2), UBound(aLinks, 1)).Value = Application.Transpose(aLinks)
        Columns("A:B").AutoFit
    Else
        MsgBox "No links were found within the active workbook.", vbInformation
    End If
    
End Sub

Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page