Invalid Links

[david-fielding]

Hello,
We are moving a load of linked files on a server consolidation, as
expected the links are no longer valid, using a global search and
replace method I have corrected the paths, however where the link is
no longer valid, renamed folders, deleted files, renamed worksheets,
etc when you open and update links any that are no longer valid
return #REF errors, all this is expected as I have done a similiar
project in the past but with only a few hundred files and I was able
to manually correct or break links to save the data if necessary.

This move has 40,000 plus files of which about 10,000 have links,
doing this manually is out of the question.

Taking a single file as an example, after doing the global search and
replace, the basic paths are now correct, open the file, don't update
links, go to links dialog, it is linked to 6 other files, update the
values, 4 are status "OK" and 2 links say are no longer valid and
status is "Error: Source not found."

What I want to do is keep the 4 valid links that are updating
correctly, and break links on the other 2 saving the data, so when the
user tests the files when the file is opened and all links are updated
there are no #REF errors, I can do this manually and get the desired
result but I can't possibly do the 10,000 linked files manually

If anyone has any ideas that would be very helpful

Cheers
David

 

[Joel]

The first think I would do is to do your global replace on all the files
first then go back and check for invalid references. This will make sure all
the links have been updated. If book1 is linked to book2 and book2 hasn't
been updated, the links in book2 will be invalid.

After all bokks have been updated now you can perform your checks for
invalid references. Make sure you check for multiple links in one cell

='C:\TEMP\[Book3.xls]Sheet1'!$A$1 + 'C:\TEMP\[Book3.xls]Sheet1'!$A$2

You can get the old value of the cell using value2
=Range("A3").value2 with cells that have links.

 

[david-fielding]

That's the point I don't want to update all the links as with any
invalid links, the data will be replaced with #REF errors and I can't
check and correct 10,000 files

David

 

[Joel]

Which files do you plan to update? Do you have plan which says which files
get updated and which files don't get updated? Your original plan appeared
that you were going to update all the files. Now your request says you can't
update all the files.

Sit down and make a plan. My last posting said that your can replace the
formula with the link with the value in VALUE2.

Still my recommendations are the same. Decide which files you are going to
update. Update the links first, then go back and the links that are
producing errors replace with values. You may want to go through replacing
links multiple times to keep as many links as possible.

The other choice is to create a recursive algorithm. That when you open a
file check for all the links and then open these files.. Repeating the
process until you don't find any more links.

 

[david-fielding]

Thanks Joel,
All together there are around 40,000 files of which about 10,000 have
links, I only need to look at the linked files I now have a list of
all the linked files, I need a way of going through the 10,000 files
and where the link is invalid break the link and maintain whatever
data is there and where the link is valid leave those links in the
workbook, using the global search and replace method on the test files
on the test server with the new mapping has corrected the link paths
but any path that is invalid and would have been invalid on the
original server had they been updated recently gives #REF errors
everywhere.

I hope this makes more sense

Cheers
David

 

[Joel]

Then, your plan is to go through 10,000 files. I said originally, fix the
links in one pass. Then go vack again to every file and check the links if
they are good. the ones that are not good replace the formula with the value
from value2.

Range("A3")=Range("A3").value2 with cells that have links.
Also set the formula in the cell to a blank string
Range("A3").formula = ""