In this article, we will show how to find External Data Connections in Excel. External Data Connections are the reference to the data in another workbook from where one is inserting the data into the existing workbook. This allows users to update their dataset according to the data from the other workbook. In this article, we will learn how to keep track of those external connections.
Find External Data Connections in Excel: 5 Ways
In this article, we will discuss 5 handy ways to find External Data Connections in Excel. Firstly, we will use the Find feature to get the links. Secondly, we will use the Name Manager. Thirdly, we will use the Edit Links command to get the connections. Then, we will use a VBA Code to find the connections. Finally, we will use the 4.0 Excel Macros to find External Data Connections in Excel. This is a sample dataset that has data connected to it from two different sheets named Number of Sale.xlsx and Unit Price.xlsx.
1. Applying Find Feature
In this method, we will use the Find feature of Excel to find the External Connections of every cell in the dataset. The Find feature allows users to look for the value or any data that they require from the dataset. It also allows them to look for External Connections.
Steps:
- Firstly, go to the Home tab.
- From there, select the Editing option.
- Then, from the available options, select Find & Select.
- Finally, choose Find from the drop-down options.
- As a result, a prompt will be on the screen.
- In the prompt, first, write “.xl” in the Find What box.
- Then, select Within workbook.
- Next, Look in Formulas.
- Finally, click on Find All.
- Consequently, we will get all the connections of all the cells of the workbook.
Thus, we will find all the External Data Connections in Excel.
2. Using Name Manager
The Name Manager option manages all the Name Ranges of the workbook. Users often add a range of data having a Name Range of a workbook into another workbook. This allows them to apply formulas effectively. In this example too, we added a name range from another workbook into our workbook.
Steps:
- At the beginning, open the “Number of Sales.xlsx” workbook.
- Then, select the B5:B10 cell range.
- Finally, write Fruits in the Name Box to name the range.
- Next, go back to the existing workbook.
- In the B5 cell, link the workbook with the “Number of Sales.xlsx” workbook by typing “=” sign and selecting the Fruits range of the “Number of Sales.xlsx” workbook as a formula.
- Thereafter, go to the Formulas tab.
- From there, choose Define Name.
- As a result, a prompt will be on the screen.
- In the prompt, write Fruit_Items as Name.
- Then, select the Fruits range from the “Number of Sales.xlsx” workbook as Refers to range of the name.
- Finally, click OK.
- Next, go to the Formulas tab.
- Then, select the Name Manager option under the Defined Names option.
- Consequently, a prompt will be on the screen.
- In the prompt, under the Refer to section, we will be able to see the External Data Connection of the existing Name Range.
You could copy this link and paste it into the Windows taskbar, and it will take you to the location of the “Number of Sales.xlsx” workbook.
Read More: How to Create Excel Data Connection to Another Excel File
3. Using Edit Links
The Edit Links option is a good way to check on, edit, or modify the External Connections. Here, we will use this option to find the links.
Steps:
- To start with, select the Data
- Next, select the Edit Link option from the Queries & Connections
- As a result, a prompt will be on the screen.
- In the prompt, you will see the name of the source of the data in the Source column.
- You could click on the Open Source option to open the source workbook.
- As a result, the “Number of Sales.xlsx” Excel worksheet will be opened.
This is how you will find and open the External Data Connections in Excel.
4. Applying VBA Code
VBA codes are a good way to do multiple tasks at a time. In this method, we will use a simple VBA Code to get all the External Connections of the workbook and show them in a new sheet.
Steps:
- Firstly, go to the Developer tab.
- From there, select the Visual Basic option.
- As a result, a new window will be on the screen.
- From the Visual Basic window, first, select
- Then, choose Module from the drop-down list.
- Consequently, a new module will appear.
- In the module, write down the following code and save it.
Sub FindExternalConnections()
Dim am_link As Variant
am_link = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(am_link) Then
Sheets.Add
For a = 1 To UBound(am_link)
Cells(a, 1).Value = am_link(a)
Next a
End If
End Sub
- Finally, run the code by clicking on the triangular-shaped green button.
- Consequently, we will get the list of External Connections in the workbook.
Like the Edit Links method, these links will also take you to the location of the workbooks.
Read More: Excel VBA: How to Refresh All Data Connections
5. Extracting All External Connections
The 4.0 Macros in Excel are the predecessors of VBA. They allow users to perform repetitive work before the introduction of VBA Codes. In this example, we will use one of the still-existing 4.0 or XML Macros to extract External Data Connections. Then, we will transpose the link list using the TRANSPOSE function.
Steps:
- Firstly, go to the Formulas tab.
- Then, select Name Manager from the Defined Names group.
- As a result, a prompt will appear on the screen.
- From the Name Manager prompt, choose
- Now, first, Name the macros.
- We will name it.
- Secondly, write LINKS() in the Refer to box.
- Finally, click on OK.
- Next, select the B5 cell and type,
- Then, hit Enter.
- As a result, we will have a list of External Data Connections.
You could put these links in the Windows search box to get the location of the External Data.
Read More: [Fixed!] External Data Connections Have Been Disabled in Excel
Download Practice Workbook
You can download the practice workbook here.
Conclusion
In this article, we have shown multiple ways to find External Data Connections in Excel. This will allow users to check out, update, break or delete the existing links to the workbook effectively. If you have any questions regarding this essay, feel free to let us know in the comments.
Related Articles
- How to Refresh Data Connection in Excel Without Opening File
- Excel Connections vs Queries
- How to Create a Data Source in Excel
- Data Connection Not Refreshing in Excel
- Excel Queries and Connections Not Working
- How to Close Queries and Connections Pane in Excel
<< Go Back to Excel Data Connections | Importing Data in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!