Migrating a lot of excel files between folders

Hello guys,

I registered here because I'm a bit lost with a task I have at hand. I want to change some directory names in a big structure but the problem is there are laying a lot of excel files there that reference each other. So if I change the structure there will be a lot errors popping up. I had the idea of scanning all those files for references, writing them out, run a script for find and replace on the old stuff and then write it back.

Here is some of the stuff I already pieced together with google and co.

To check for links in a file and write them out.

Please Login or Register  to view this content.

Then I run the following code to change all the references.

Please Login or Register  to view this content.

Now I'm lost for two things. What is the best way to write all those formulas back into their stuff and the second bigger thing, how do I make this thing dynamic that it does this stuff over whole folders with hundreds of excel files?

For the writing back stuff I now have a new sheet in my excel file with the following structure:

SheetName, Index of Cell, Formula

So my idea is to convert everything to an array and then do something like

sheet name!index = formula

in a loop over the full array.

But I fear that this isn't very practical from a performance point of view with 8000 formulas in a file.

This should work:
To deal with errors in links:
If the link file is not in the folder or the path does not exist, Excel makes you set it up at the time.
To prevent this happening when the code is running, the code switches off Display Alerts, and writes a note in column D if there is an error. It writes the formula (with its original link) in column E and puts text "Link Not Found" in column D.
The original formula is written back to the linked cell.
It should run quickly


Insert it in Sub ReplaceEPP4_grob AFTER line

Please Login or Register  to view this content.

Please Login or Register  to view this content.

After I killed the "Option Explicit" it seems to work but it takes really Long. The example file I used it on has something like 2500 formulas with links in it.

I will give it a try. Thanks a lot.