Hardcore users only - help needed to clean up a Workbook

[ecovonrein]

I attach a file that drives me mad.  I just reported the same to Microsoft.  The file is a totally pared back production file.  It is substantially empty - I deleted practically all.  (The single Worksheet is brand new.) When you pull it up (Enable Content), see Data/Workbook links - you should see two.  Now press ALT-F8 and run the only macro in the Workbook ("unhide...").  Check Data/Workbook links again - the links will have disappeared.  Look at the trivial macro (ALT-F11) - it does nothing but to iterate over all defined Names.  If you want, rebuild the environment in a totally new Workbook.  Copy the macro.  No problem - the links survive.  Sadly, the production sheet is so big that rebuilding it from scratch is not an appealing prospect... 😞

Thanks for your help.

PS:  The external references are created by 4 defined Names.  And you will easily see (by inspecting the Name Manager) that these references remain, even as Excel believes they have vanished.

Update #1:  The attachment is larger than one would expect (from an empty Workbook).  This is owed to production data being cached by the links.  I just repointed those links to an empty target.  It does reduce the file size to 30kB.  The problem remains the same.

[#2]

@ecovonrein 

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

Maybe that helps.

If not, please include more detailed information, such as Excel version, operating system, storage medium, etc.

[#3]

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

[#4]

I found a work-around.  My Workbooks generally make no grid references to foreign Workbooks.  All such references are handled thru the Name Manager via defined Names (just like in the example attached).  However, it turns out that explicit references to those foreign Workbooks from within the grid survive the bug discussed here.  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).

Stupid but manageable. 

[#5]

@ecovonrein 

Remove hidden data and personal information by inspecting documents, presentations, or workbooks

"Ghost links" in excel

Removing Unknown Links

Maybe these links will help you a little further with your topic.

fyi

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

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.

Here is a refined version of your macro to attempt the same idea (preserving links):

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, "[") > 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

Hope I helped a little...if not just ignore my post.🙂

[#6]

@NikolinoDEWell, I would certainly be curious to learn Excel 2016's response.  You do not say.  When you run the unhide macro, do all links vanish from your Excel too?  My suspicion was that the problem is somehow related to my rich use of Lambdas in M365 - something 2016 never heard of.  Then again, I went out of my way to clear any trace of my previous use of LET or LAMBDA...

PS:  Your macro to create a hidden Worksheet in which to insert grid references to foreign Workbooks occurred to me too.  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.  Thanks.

PPS:  As I wrote before, I have the exact opposite problem from the references you share.  I have real links - no "ghosts" - which Excel loses track of...

[#7]

A further update re the work-around.  The reason it works is the same reason I am now in a different pickle.  The codebase of Excel must be a complete mess after 40+ years of maintenance.  It appears that external references in the grid are handled differently from those in .Names.  I noticed this because our files are all in SPO.  And the issue I had for the longest time with this environment is that when I am on the road and without access to SPO, the environment is too dumb to seamlessly switch to (the copies on) OD.  So, we run some VBA code at start-up that automatically aligns the external references with the source of the Workbook.  If the latter is opened from SPO, fine, all links are pointed to SPO; when the latter is opened from OD, all links are pointed to OD.  This solved the travelling problem just until this work-around.  It transpires that Auto_Open does not run before Excel attempts to resolve external grid references with the result that Excel simply hangs itself in the absence of SPO when one of those references points to SPO... 😞

And while on that subject, though somewhat off-topic, the SPO handling by Excel is total rubbish too.  When Workbook A links to Workbook B and I save Workbook B to a new filename on OD, then EVERYTHING in Workbook A will look like it now points to the new Workbook B on OD, as it should.  However, Excel has surreptitiously ignored my instruction to reference OD and instead inserts references to SPO.  You know now how I noticed... 😞

[#8]

@ecovonrein 

You've encountered the limitations of Excel's old design, particularly when handling cross-workbook links and environments like SPO and OD.

Your observations highlight some deep-seated issues within Excel’s codebase, especially with the divergence between how grid-based references and named references are handled, along with the complexities of auto-updating links across different environments.

From what I understand, you have encountered a really frustrating point with Excel's handling of external references, especially when integrating SharePoint Online (SPO) and OneDrive (OD).

Perhaps by adjusting the timing of your VBA procedures (using Workbook_Open and Workbook_BeforeClose) and implementing centralized link fixing logic, you should (in theory) be able to minimize the noise caused by Excel trying to resolve links before executing your code. This is all theoretical, because it's a bit beyond my knowledge, to be honest. Maybe you could add AI to your topic about this.🙂

[#9]

@NikolinoDECalling Auto_Open from Workbook_Open may be the answer.  I am trying to verify this.  Thanks.