Hardcore users only - help needed to clean up a Workbook

Question

I attach a file that drives me mad.&nbsp; I just reported the same to Microsoft.&nbsp; The file is a totally pared back production file.&nbsp; It is substantially empty - I deleted practically all.&nbsp; (The single Worksheet is brand new.) When you pull it up (Enable Content), see Data/Workbook links - you should see two.&nbsp; Now press ALT-F8 and run the only macro in the Workbook ("unhide...").&nbsp; Check Data/Workbook links again - the links will have disappeared.&nbsp; Look at the trivial macro (ALT-F11) - it does nothing but to iterate over all defined Names.&nbsp; If you want, rebuild the environment in a totally new Workbook.&nbsp; Copy the macro.&nbsp; No problem - the links survive.&nbsp; Sadly, the production sheet is so big that rebuilding it from scratch is not an appealing prospect... 😞&nbsp;Thanks for your help.&nbsp;PS:&nbsp; The external references are created by 4 defined Names.&nbsp; And you will easily see (by inspecting the Name Manager) that these references remain, even as Excel believes they have vanished.&nbsp;Update #1:&nbsp; The attachment is larger than one would expect (from an empty Workbook).&nbsp; This is owed to production data being cached by the links.&nbsp; I just repointed those links to an empty target.&nbsp; It does reduce the file size to 30kB.&nbsp; The problem remains the same.

nikolinode · Answer

ecovonrein&nbsp;This behavior could be related to hidden or "phantom" external links, but I'm not sure, as several pieces of information are not conclusive to me. These are notoriously tricky, especially with large, older workbooks that may have accumulated references or objects over time. In the pasted file, you will find your macro with a small change "For i = 1 To n-Loop" and a macro with "PhantomExternalLinks".&nbsp;Maybe that helps.&nbsp;If not, please include more detailed information, such as Excel version, operating system, storage medium, etc.

ecovonrein · Answer

NikolinoDEThanks for engaging with this issue.

I do not believe that STEP -1 necessarily makes your code any more robust than STEP +1. It all depends on the implementation of the underlying Collection. To be truly safe, you would need to take a copy of the Collection. But your code and mine differ in that your deletions of Names actually change the underlying Collection; my mere change of an attribute of a Name does not.

My original code used FOR EACH when it ran up against this problem. I was merely clutching at straws when I changed it to FOR i. To no avail.

But your post has me confused. Were you able to replicate my issue on your system? If so, why would my environment (Win 10, M365, Build 2406) be of interest? More importantly, did your code solve the issue in your environment? For it sadly does not do so here.

Incidentally, your code does not so much chase phantom links as it attempts to work around the strange situation where in this particular Workbook thisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) suddenly collapses to 0 after some macro loops over all Names. That is, the link your code finds is no "phantom"; it is very real. (And I have no interest in deleting it - I need it.) My issue is that Excel loses track of it.

ecovonrein · Answer

I found a work-around.&nbsp; My Workbooks generally make no grid references to foreign Workbooks.&nbsp; All such references are handled thru the Name Manager via defined Names (just like in the example attached).&nbsp; However, it turns out that explicit references to those foreign Workbooks from within the grid survive the bug discussed here.&nbsp; So I must insert into my spreadsheet somewhere a single explicit reference like "='[foreignWorkbook]someSheet'!A1" FOR EVERY foreign Workbook referenced to preserve thisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks).&nbsp;Stupid but manageable.&nbsp;

nikolinode · Answer

ecovonrein&nbsp;Remove hidden data and personal information by inspecting documents, presentations, or workbooks"Ghost links" in excelRemoving Unknown LinksMaybe these links will help you a little further with your topic.&nbsp;fyiI opened your file in Excel 2016.You can try to create a new, simple workbook with external links and defined names referencing other workbooks. Run the same macros on this simpler file to see if Excel loses track of links here as well. If not, the issue may lie with the complexity or corruption of the original workbook.&nbsp;Your point about using FOR EACH or FOR i is valid. While iterating backwards (using STEP -1) helps prevent issues when modifying a collection, the problem here seems unrelated to the iteration method. Rather, as far as I have interpreted the translation correctly, to be due to a bug related to how Excel handles external links when working with defined names.&nbsp;Here is a refined version of your macro to attempt the same idea (preserving links):&nbsp;Option Explicit
'The code is untested, backup your file first.
Sub preserveExternalLinks()
    Dim n As Long, i As Long
    Dim nm As Name
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim extLinks As Collection
    Set extLinks = New Collection

' Create a hidden worksheet to store temporary link references
    On Error Resume Next ' If sheet already exists
    Set ws = ThisWorkbook.Sheets("TempLinkSheet")
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add
        ws.Name = "TempLinkSheet"
        ws.Visible = xlSheetVeryHidden ' Hide it from the user
    End If
    On Error GoTo 0

lastRow = 1
    n = ThisWorkbook.Names.Count
    
    ' Iterate backwards to avoid issues with index shifting
    For i = n To 1 Step -1
        Set nm = ThisWorkbook.Names(i)
        
        ' If the name refers to an external workbook (links contain brackets [ ])
        If InStr(nm.RefersTo, "[") &gt; 0 Then
            ' Store the external reference in a hidden worksheet
            extLinks.Add nm.RefersTo
            ws.Cells(lastRow, 1).Formula = nm.RefersTo
            lastRow = lastRow + 1
        End If
        nm.Visible = True
    Next i
    
    MsgBox "External links preserved and handled."

End Sub&nbsp;&nbsp;Hope I helped a little...if not just ignore my post.🙂

ecovonrein · Answer

NikolinoDEWell, I would certainly be curious to learn Excel 2016's response.&nbsp; You do not say.&nbsp; When you run the unhide macro, do all links vanish from your Excel too?&nbsp; My suspicion was that the problem is somehow related to my rich use of Lambdas in M365 - something 2016 never heard of.&nbsp; Then again, I went out of my way to clear any trace of my previous use of LET or LAMBDA...&nbsp;PS:&nbsp; Your macro to create a hidden Worksheet in which to insert grid references to foreign Workbooks occurred to me too.&nbsp; The algorithm would need to be somewhat more complex than you show, and I rejected it for that reason in favour of inserting a few manual links.&nbsp; Thanks.&nbsp;PPS:&nbsp; As I wrote before, I have the exact opposite problem from the references you share.&nbsp; I have real links - no "ghosts" - which Excel loses track of...

Forum Discussion

Hardcore users only - help needed to clean up a Workbook

Resources