VBA - Break ALL external links

[excelstarter1]

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

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

 

[excelstarter1]

In addition:

(3) Some links are stored within conditional formatting (I copied one worksheet to a new workbook which contained conditional formatting. And now the conditional formatting is linked to that external worksheet). How can I detect if conditional formatting contains either an error (#REF) or an external link and if so how can I delete the conditional formatting?

 

[excelstarter1]

For 1, I will use:

    Sub RemoveDataValidation_ActiveBook()

      Dim sh As Worksheet

      For Each sh In Worksheets

        sh.Cells.Validation.Delete

      Next sh
      
    End Sub

 

[Beyond_avarice]

There are a couple items that could be causing a failure of the "Edit Links" wizard from allowing you to break the links.

This should help you remove the Data Validation Links.

Sub dataValidateBreak()

Dim dVal As Object

For Each dVal In ActiveSheet.Cells().SpecialCells(xlCellTypeAllValidation)

    On Error Resume Next
    
    If InStr(1, dVal.Validation.Formula1, "REF") > 1 Then
            dVal.Validation.Delete
        ElseIf InStr(1, dVal.Validation.Formula1, "[") > 1 Then
            dVal.Validation.Delete
    End If

      
Next

End Sub

Another problem can be Defined names. I've put this together to create a tab that will identify all Defined names. You will then place an "x" in column "B" of the respective name you want to have deleted. Comment out the Sheet.Add and uncomment the "IF statement" then rerun this.

Sub bulkHiddenNameRemove()

Dim nm As Name

i = 0
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "TXT"    'Comment this out after deciding what names to keep
'
For Each nm In ActiveWorkbook.Names
    Debug.Print nm.Name         '   Name has to preclude the sheet name?

'   If Worksheets("TXT").Range("B1").Offset(i, 0).Value = "x" Then          'uncomment this if statement when you have assigned an "x" to column "B" of what 
'            nm.Delete                                                                              'Defined names you want to delete
'    End If

  
   Worksheets("TXT").Range("A1").Offset(i, 0).Value = nm.Name    'Populate first row with Name Range for PDF fields
   
'
 i = i + 1

    
  Next nm
End Sub

Another problem is Conditional formats that were copied over from other workbooks. I am currently asking for help on this script as it seems to take a couple passes to fully work.

Sub condFormLinkBreak3()
Dim cRule As String                 'ConditionalFormat Rule
Dim cForm As Object                 'ConditionalFormat Range applied to
Dim asw As Worksheet
Application.ScreenUpdating = False

Set asw = ActiveSheet
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "TXT"
'
asw.Activate
z = 1
For Each cForm In ActiveSheet.Cells().FormatConditions
    On Error Resume Next
    'Debug.Print cForm.Formula1
    'Debug.Print cForm.AppliesTo.Address
   
    Sheets("TXT").Range("A" & z).Value = cForm.AppliesTo.Address
    Sheets("TXT").Range("B" & z).Value = """" & cForm.Formula1 & """"
    z = z + 1
    
    On Error Resume Next
    cRule = ""
    cRule = cForm.Formula1
            If InStr(1, cRule, "[") > 0 Then
                    asw.Range(cForm.AppliesTo.Address).FormatConditions(1).Delete  'delete anything with "["
            End If
    
Next
   
Application.ScreenUpdating = True
End Sub

 

[excelstarter1]

Great, thank you very much for your reply and input.

Data Validation is working smoothly, except for runtime error 1004 when I get rid of "On Error Resume Next".
Should dVal be an object? Nevers used objects before to be honest...

I will try out the conditional formats and let you know.

But my biggest headache remains with the links to external sheets within charts. Do you have an idea how to handle this issue?

(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.

 

[Beyond_avarice]

When a chart disconnects from it's data source; it changes it's formula to address actual data to use

eg: =SERIES(Sheet1!$I$8,Sheet1!$H$9:$H$18,Sheet1!$I$9:$I$18,1)

Becomes this when you are referencing a different workbook: =SERIES(,[Book2]Sheet1!$A$3:$A$12,[Book2]Sheet1!$B$3:$B$12,1)

Changes to the actual array of data when the other workbook is disconnected: =SERIES(,{42736,42737,42738,42739,42740,42741,42742,42743,42744,42745},{5,4,5,8,9,5,4,5,2,3},1)

The "Chart" no longer is linked. But what you might have is a hidden name or xll( if I recall that correctly) , in the background that is still trying to link.

Try running my "bulkHiddenNameRemove" script. As an example; this is what I found in a workbook my company just tasked me to clean up.

[TABLE="width: 488"]
<tbody>[TR]
[TD]DEC-15'!_3__123Graph_CCHART_4H[/TD]
[/TR]
[TR]
[TD]FEB-16'!_3__123Graph_CCHART_4H[/TD]
[/TR]
[TR]
[TD]JAN-16'!_3__123Graph_CCHART_4H[/TD]
[/TR]
[TR]
[TD]_3__123Graph_CCHART_4H[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

I just ran it and was able to remove those links that the Link Wizard was incapable of doing.