I have been using Excel for a long time and pushed it to its limits, but this one is new, probably because of a recent update, so I am not sure anyone will encounter the issue I am raising up here.
I have several crossed referenced workbooks in formulas, these workbooks being saved in the same folder so the external links are relative in this folder (and supposedly unaffected by this folder being renamed...).
I recently noticed that cells using some of these referenced workooks in their formulas could not be evaluated anymore and returned an error as if the workbooks have been moved. In the formulas, the path of these referenced workbooks which should start with C:\... now starts with file:///C:\....
If I open the Edit Links window, I can manually update the target workbooks (selecting the same workbooks) and the formulas work again.
If I replace the file:/// text by an empty string to remove them, they also work again.
Using this code in the VBE's Immediate window also reveals broken links:
?ActiveWorkbook.Name: ?"EXTERNAL LINKS: ": For Each l in ActiveWorkbook.LinkSources(XlLinkType.xlLinkTypeExcelLinks): ?" "&l: Next
returns C:\File1.xlsx or file:///C:\File2.xlsx
If I save the workbooks after that and reopen them, the links are broken again.
What is causing this?
By inspecting the content of an unzipped workbook, and more particularly the
externalLink_.xml.relsfiles, I noticed the external links was not relative at all, the broken links looked like this:So it seems that Excel uses a shorten path (like MS-DOS/Windows 8.3 filenames), but later it "forgets" to remove the prepended "file:///" string when "resolving" the shortcut in the path.
How to fix
Save and close the workbooks if not already done, shorten the path (rename a folder with a name too long for instance), reopen the workbooks, update the broken links, save, and voilà!
PS: some concepts are between quotes, feel free to correct me...