Forum Discussion
Solved
Searching for external links in excel workbook
I found there is a link to helping with this already (see below) however when following the same process it just says "We couldn't find what you were looking for. Click Options for more ways to searc...
To search for external links in an Excel workbook, you can use the "Edit Links" feature. Here are the steps:
- Open the Excel workbook that you want to search for external links.
- Go to the "Data" tab in the Excel ribbon, and click on "Edit Links" in the "Connections" group.
- The "Edit Links" dialog box will open, which shows all the linked workbooks that are used in the current workbook. This dialog box will show you all the external links as well as the internal links.
- To see only the external links, click on the "Status" column heading to sort the links by status. External links will have a status of "Source not found" or "Error".
- If you want to update or change an external link, select it and click on the "Change Source" button.
- If you want to break an external link, select it and click on the "Break Link" button.
By following these steps, you can easily search for external links in your Excel workbook and manage them as needed.
About your link:
You can find external links in an Excel workbook by using the Find and Replace feature.
Here are the steps to do so:
- Select all the cells in your workbook.
- Go to the Home tab –> Editing –> Find & Select –> Find.
- In the Find and Replace dialog box, enter *.xl* in the ‘Find what’ field.
- Click on Find All.
This will find and show all the cells that have external links in them.
Hope it helps you!
External links may also be hidden in the data validation tool, in the settings-formula part, for instance as a source for the list values. This cannot be revealed by making a search using find menu.
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 SubHow This Code Works:
- Formulas: It searches each cell in every worksheet to check if the formula contains an external reference (denoted by [ in the formula).
- Named Ranges: It checks all named ranges to see if their reference points to an external workbook.
- 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).
- Data Validation: It checks cells with data validation and looks at the Source field in the Data Validation settings to identify external links.
- Hyperlinks: It checks both traditional cell hyperlinks and hyperlinks embedded in shapes for external links.
How to Use This Code:
- Press Alt + F11 to open the VBA editor.
- Click Insert > Module.
- Copy and paste the code above into the new module.
- 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.
- This is beautifully written and clear. I found two problems with it however:
Checking for the presence of a square bracket "[" isn't a foolproof way to detect an external link, since square brackets are also used to delineate column names in references to tables. This is annoying, but could be circumvented by using a string that's specific to the external reference you're trying to locate.
A second, bigger problem occurs during the check for External Links in Data Validation. Any cell that has no data validation assigned returns "Application Defined or Object Defined error" upon trying to read *any* property of the validation object. Since Excel offers no test to see whether a validation object exists for a cell, there's no way to tell except try it, which triggers this error. Unfortunately, even using an error handler to circumvent the check doesn't seem to work for me, as "On Error Goto" doesn't actually trap this error for some reason. - I used this script and worked, this is awesome!!! \o/
