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... 😞
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.🙂
NikolinoDECalling Auto_Open from Workbook_Open may be the answer. I am trying to verify this. Thanks.
