Excel Template has reference invalid links that I can't break-CodePudding

I am in the process of creating a new version of the Template. Excel 2019 Windows 10 64 bit. I have already gone through the exercise of creating a new template and copying the worksheets one at a time. There were a number of references that I found and deleted. Many of them were in the Names dictionary. I am now down to 2 that should not have been present in the first place because they are only to be used in the workbooks that are created from the templates.

I have tried and exhausted all of the samples in questions that I have found. It is distilled into the following code:

`Public Sub FindLinks()
    Dim i As Integer
    Dim olinks As Object
    Dim v() As Variant
    Dim strLink As String
    v = ActiveWorkbook.LinkSources(xlExcelLinks)
    For i = 1 To UBound(v)
        strLink = v(i)
        Debug.Print i, "Link = "; strLink
        ' Now break the link
        ActiveWorkbook.BreakLink Name:=strLink, Type:=xlLinkTypeExcelLinks
    Next i
End Sub`

The above example finds two links and displays them but will not break the links - just does nothing.

Anyone have a notion of what I could try next?

CodePudding user response：

The standard "Break Links" route does not capture links that are referenced through:

Named ranges
Conditional formatting
Data validation
Graph sources
Pivot Table sources

If you have any of these components or functionalities, look through their corresponding interfaces (e.g. the Name Manager for named ranges, etc.) and check for linked content.

CodePudding user response：

SIE_Vict0ria - Thank you. I had actually fixed all the references in the Name Manager, but I discovered the two references that were "invalid" were in combo-boxes. I deleted them from the combo boxes and voila, no more external references at all. So the problem is fixed.

One thing I do not know, however, is why those references were bogus. The spreadsheets being references were just where they were supposed to be and were shared and marked full control to everyone. I did not find any documentation on why a reference would be marked as "invalid".

I hope this helps others when they are chasing this kind of problem.