Forum Discussion
Hardcore users only - help needed to clean up a Workbook
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.
Remove hidden data and personal information by inspecting documents, presentations, or workbooks
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.🙂