Broken external links in Excel a leading file scheme appended to paths can t be evaluated in formulas

Question

I have long used Excel and have tested its limits, but this one is brand-new, likely as a result of a recent update, so I doubt anyone will run across the problem I am highlighting here.

I have a number of workbooks with cross-references in my formulas, all of which are saved in the same folder, making the external links in this folder relative (and supposedly unaffected by this folder being renamed...).

Recently, I discovered that cells with formulas referencing some of these linked workbooks could no longer be evaluated and would instead produce an error as if the workbooks had been transferred. The path of these referenced workbooks, which should begin with C:... in the formulas, now begins with file:/C:....

The formulas resume working if I enter the Edit Links window and manually edit the target workbooks (selecting the same workbooks).

They also function once I delete them by replacing the file:/// text with an empty string.

Broken links can also be found using this code in the Immediate window of the VBE:

?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?

Answer 1

If not already done, save and close the workbooks, reduce the path (for example, rename a folder with a long name), open the workbooks again, fix any broken links, save, and you're done.