Forum Discussion
Hardcore users only - help needed to clean up a Workbook
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.
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... 😞