Alok Paul
Sometimes we use the information of one Excel file for other. This way, we link Excel files one to another. Sometimes we see that, there are unknown links are present in the Excel file. In this article, we will discuss how to remove those unknown links in Excel and also the other external link with the proper illustration.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Examples to Remove Unknown Links in Excel
In the sample dataset, we can see that there are two external links Cell D5 and D9. In this article, we will show how to indicate those external links and then remove them.
1. Remove Unknown Links from Cells
Links may exist in Excel in different forms. Here, we will show how to remove unknown links from cells. We will use the Data feature of Excel to remove links.
📌 Steps:
- Click on the Data tab first.
- Choose Edit Links from the Queries & Conditions group.
- The Edit Links window appears. We see the source link is showing here.
- Click on the Break Link options.
- A warning dialog will show here. Choose the Break Links option.
- Again, look at the Edit Links window. Then, click on the Close option.
The attached link has been removed successfully.
- Now, move the cursor to Cell D5.
We can see no links are showing here.
Read More: How to Remove Hyperlink from Excel (7 Methods)
2. Remove Unknown Links from Shapes
In this section, we have an oval shape. Also, the shape consists of an external link. We will remove this link here.
At first, we will check how many unknown links are present in the sheet.
📌 Steps:
- Press the F5 button and the Go To window will appear.
- Click on the Special button.
- The Go To Special window appears.
- Choose the Objects option.
- Then, press the OK button.
- Now, all the external links will be selected. We can see only one unknown link present in the sheet.
- Press the right button of the mouse.
- Choose the Remove Link option from the Context Menu.
The unknown link has been removed from the shape.
Read More: How to Remove External Links in Excel
3. Remove Unknown Links from Named Range
In this section, we will remove unknown links from Named Range.
We can see that in the dataset, the values of the Salary column are taken from a named range.
📌 Steps:
- Now, we will check the named range. Click on the Formulas tab.
- Choose the Name Manager option.
- The Name Manager window appears.
We can see there is an external source here. If we remove this external source from the named range the cells will be blank or values will remove also.
Read More: How to Remove Hyperlink for Entire Column in Excel (5 Ways)
4. Remove Unknown Links Using VBA
In this section, we will apply the VBA macro to remove unknown links. It also shows the number of unknown links that are removed from the file.
📌 Steps:
- First, go to the bottom of the Excel Sheet.
- Press the right button of the mouse.
- Choose the View Code option from the Context Menu.
- We enter the VBA window.
- Choose Module from the Insert tab.
- This is the VBA module. We will write VBA code here.
- Copy and paste the following VBA code on the module.
Sub Remove_Unknown_Links()
Dim Unknown_Links As Variant
Dim N, Remove_Link As Long
Unknown_Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
If IsEmpty(Unknown_Links) = True Then GoTo ReportResults
For N = 1 To UBound(Unknown_Links)
ActiveWorkbook.BreakLink Name:=Unknown_Links(N), Type:=xlLinkTypeExcelLinks
Remove_Link = Remove_Link + 1
Next N
ReportResults:
MsgBox "Unknown Links Removed: " & Remove_Link
End Sub
- Press F5 to run the code.
A dialog box will appear. It presents the number of unknown links in the file.
- Go to the sheet.
We can see unknown links have been removed.
Code Explanation:
Dim Unknown_Links As Variant
Dim N, Remove_Link As LongDeclare the variables.
Unknown_Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)Set the active workbook as the value of one variable and check whether it consists of any links or not.
If IsEmpty(Unknown_Links) = True Then GoTo ReportResultsIf there is no link, then go to the ReportResults section.
For N = 1 To UBound(Unknown_Links)Check the links to the variable.
ActiveWorkbook.BreakLink Name:=Unknown_Links(N), Type:=xlLinkTypeExcelLinksConverts the links into values.
Remove_Link = Remove_Link + 1Remove the link.
Next NGo to the next link.
MsgBox "Unknown Links Removed: " & Remove_LinkPrint a text and the number of links.
Read More: How to Remove Hyperlink Permanently in Excel (4 Ways)
Conclusion
In this article, we described how to remove unknown links from the Excel file. We used a VBA code that indicates the number of links in the file also. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
