I am looking for a way to override the automatic addition of the directory path to external links when a workbook is saved.
The location of the linked workbook (linkEE) is always known to the workbook with the links (linkER). LinkEE maybe in either a a directory specified in a parameter file, or the same directory as linkER.
The Workbook_Open event of linkER looks for linkEE, and opens and hides it, or alerts the user to its absence (only when needed).
This works fine while the workbook locations remain static. But when the locations change it all falls apart. Typicallly this happens when a user needs to work at home on copies of workbooks from the work LAN environment, and later return them to the LAN. It would not be an issue if they knew how to change links, but they don’t, and won’t.
Although linkER knows where linkEE is, and opens it OK, the links fail because Excel stores the directory path as part of the external link and wants to access linkEE from where it was last time linkER was saved, rather than use the one that is already open.
Of course, both linkER and linkEE are large and complex, the links are both direct and indirect (via OFFSETs, INDIRECTs, and dynamic named ranges), and the user level is at where they will 
at a hint of “The workbook you opened contains links to… “.
Since the location of linkEE is known, a possible workaround would be to write code to “refresh” the link directory paths during Workbook_Open. However, I’m reluctant to pursue this path 
due to the varied link types, their number (thousands), and the limited “grunt” of most user PCs. Alternatively, code in the Workbook_BeforeSave event could remove the link directory paths, but is still far from ideal.
Anyone have a more elegant solution, with less 
?
I was afraid that might be the answer 
.
, while waiting a bit longer in case anyone else has an 
.
people around here, in addition to the invaluable search facility.
