Searching for external links in excel workbook

ngokcek 

To cover all possible sources of external links in an Excel workbook — including formulas, named ranges, charts, data validation, and objects — you can use a comprehensive VBA script that searches across multiple elements in the workbook.

VBA code is untested backup your file first.

Sub FindAllExternalLinks()
    Dim ws As Worksheet
    Dim c As Range
    Dim lnks As String
    Dim nm As Name
    Dim ch As ChartObject
    Dim dv As Validation
    Dim hl As Hyperlink
    Dim shp As Shape
    Dim chk As Boolean
    Dim msg As String
    
    ' Start with an empty string to collect external links
    lnks = ""
    
    ' 1. Check for External Links in Formulas
    For Each ws In ThisWorkbook.Sheets
        For Each c In ws.UsedRange
            If InStr(1, c.Formula, "[") > 0 Then
                lnks = lnks & "External link in Formula in " & ws.Name & "!" & c.Address & ": " & c.Formula & vbCrLf
            End If
        Next c
    Next ws
    
    ' 2. Check for External Links in Named Ranges
    For Each nm In ThisWorkbook.Names
        If InStr(1, nm.RefersTo, "[") > 0 Then
            lnks = lnks & "External link in Named Range '" & nm.Name & "': " & nm.RefersTo & vbCrLf
        End If
    Next nm
    
    ' 3. Check for External Links in Charts
    For Each ws In ThisWorkbook.Sheets
        For Each ch In ws.ChartObjects
            If InStr(1, ch.Chart.SeriesCollection(1).Formula, "[") > 0 Then
                lnks = lnks & "External link in Chart '" & ch.Name & "' in " & ws.Name & vbCrLf
            End If
        Next ch
    Next ws
    
    ' 4. Check for External Links in Data Validation
    For Each ws In ThisWorkbook.Sheets
        For Each c In ws.UsedRange
            On Error Resume Next
            Set dv = c.Validation
            If Not dv Is Nothing Then
                If InStr(1, dv.Formula1, "[") > 0 Then
                    lnks = lnks & "External link in Data Validation in " & ws.Name & "!" & c.Address & ": " & dv.Formula1 & vbCrLf
                End If
            End If
            On Error GoTo 0
        Next c
    Next ws
    
    ' 5. Check for External Links in Hyperlinks (including Shapes and Objects)
    For Each ws In ThisWorkbook.Sheets
        ' Check normal hyperlinks in cells
        For Each hl In ws.Hyperlinks
            If InStr(1, hl.Address, "http") > 0 Or InStr(1, hl.Address, "[") > 0 Then
                lnks = lnks & "External link in Hyperlink in " & ws.Name & "!" & hl.Parent.Address & ": " & hl.Address & vbCrLf
            End If
        Next hl
        
        ' Check for hyperlinks in shapes
        For Each shp In ws.Shapes
            If shp.Type = msoHyperlink Then
                If InStr(1, shp.Hyperlink.Address, "http") > 0 Or InStr(1, shp.Hyperlink.Address, "[") > 0 Then
                    lnks = lnks & "External link in Shape Hyperlink '" & shp.Name & "' in " & ws.Name & ": " & shp.Hyperlink.Address & vbCrLf
                End If
            End If
        Next shp
    Next ws
    
    ' Show results in a message box
    If lnks <> "" Then
        msg = "External links found:" & vbCrLf & lnks
    Else
        msg = "No external links found in the workbook."
    End If
    MsgBox msg, vbInformation
End Sub

How This Code Works:

1. Formulas: It searches each cell in every worksheet to check if the formula contains an external reference (denoted by [ in the formula).
2. Named Ranges: It checks all named ranges to see if their reference points to an external workbook.
3. Charts: It inspects each chart to see if the chart's data series refers to external data (again denoted by [, which means the data comes from an external file).
4. Data Validation: It checks cells with data validation and looks at the Source field in the Data Validation settings to identify external links.
5. Hyperlinks: It checks both traditional cell hyperlinks and hyperlinks embedded in shapes for external links.

How to Use This Code:

1. Press Alt + F11 to open the VBA editor.
2. Click Insert > Module.
3. Copy and paste the code above into the new module.
4. Press F5 or go to Run > Run Sub/UserForm to execute the code.

The script will search the workbook for external links and display them in a message box.

Hope this will help you.