excelstarter1
Board Regular
- Joined
- Jul 20, 2017
- Messages
- 81
Hey guys,
I read quite a few posts about breaking or delinking links to external sources. I found quite a handy code, see below, to break links the easy way. However, I am stuck with a couple of annoying links to external sources:
(1) Some links are stored within data validation (I copied one worksheet to a new workbook which contained data validation. And now the data validation is linked to that external worksheet). How can I detect if a data validation contains either an error (#REF) or an external link and if so how can I delete the data validation?
(2) Even worse are the links to external sources within charts which I cannot break using the code below. Do you have an idea how I can check each chart in my workbook if it contains EXTERNAL links (I definitley want to keep INTERNAL links within my workbook)? And if it contains external links I want to substitute hard-coded values.
Thanks in advance!
Regards
I read quite a few posts about breaking or delinking links to external sources. I found quite a handy code, see below, to break links the easy way. However, I am stuck with a couple of annoying links to external sources:
(1) Some links are stored within data validation (I copied one worksheet to a new workbook which contained data validation. And now the data validation is linked to that external worksheet). How can I detect if a data validation contains either an error (#REF) or an external link and if so how can I delete the data validation?
(2) Even worse are the links to external sources within charts which I cannot break using the code below. Do you have an idea how I can check each chart in my workbook if it contains EXTERNAL links (I definitley want to keep INTERNAL links within my workbook)? And if it contains external links I want to substitute hard-coded values.
Thanks in advance!
Regards
Code:
Sub BreakExternalLinks()
'PURPOSE: Breaks all external links that would show up in Excel's "Edit Links" Dialog Box
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault
Dim ExternalLinks As Variant
Dim wb As Workbook
Dim x As Long
Set wb = ActiveWorkbook
'Create an Array of all External Links stored in Workbook
ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
'Loop Through each External Link in ActiveWorkbook and Break it
For x = 1 To UBound(ExternalLinks)
wb.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x
End Sub