Excel 2003 Formula Path Issue

Hello Everyone,

One of my users has some 8,000 excel spread sheets that all link to a master file. When the files were created they used a static path to the master file 'E:\data\COA2DE~1\file.xls' where the true path was something like 'E:\data\EstimateCosting2004\file.xls'. The problem is that the files got moved and the folder deleted. When it was moved back none of the links work any more. I had to recreate the folder and move all of its contents back in. The directory structure is exactly the same. What am I missing here, and how could I get this working right again without going through each excel document and changing the 50 cells that used the link?


Thank you,

 

Try this (you'll need to do it for each file, but not for each link):

1. Open a workbook that contains the link to the lost source (that is, the workbook that doesn't know where to look for its data, and you want to tell it).
2. On the Edit menu, click Links.
3. In the Source box, click the name of the link whose source you would like to change.
4. Click Change Source.
5. In the Change Source dialog box, click the source workbook

You may be able to write a macro to do this.
I don't know of any easier way.
Unless you can change the source file to the name that is currently set as the source. A bit hard to say without seeing it.

 

I have just done a search on "fix repair broken excel links". It turned up some stuff that might be of interest. I haven't used it and can't recommend anything though.