Linking or grabbing data from 400 workbooks

[uncleslinky]

I have a worksheet (FINAL.xls) which has a list of 400 branches in column a, each branch has 20 rows. Therefore I have 8000 rows in total. I then have a workbook for each branch (BRANCH1.xls, BRANCH2.xls) which are stored in C:\

The information in each of these sheets needs to be fed through to FINAL.xls in columns B,C,D and be put next to the correct Branch.

I tried to do this by linking cells, however takes a HUGE amount time to update the values as their would be 8000 links.

Is there a better way to perform this? I'm not sure if there is a macro that could perhaps open all the workbooks in the folder in my C:/ and grab the values...? I wouldn't be sure how to do this other than have to write the macro 400 times to find each branch (im not sure if someone knows a way?)

Also, there wouldn't be 400 files in the folder all at the same time so it hopefully wouldn't go too slow and wouldn't have to open all 400 files after eachother - the files come into that folder at different times.

Any help or ideas would be great
Thanks

PS. I have attached the sheets as an example

[TMS]

Based on what you have said, I'd take the following approach.

Rough outline process:

Navigate to the folder
For each file in the folder:
add the file name to a check list
first time round (list empty) - add all files to the list
subsequent times (list populated) - check list and only add new files to the list
Work through the list of files
For each file not previously "processed"
open the file
extract the data
update the master list
close the file
mark the file name on the check list as "processed"
Perform any calculations
Format the data, if necessary
Save the master data file
Close the master data file

This assumes that files are "finalised" before being added to the folder

The process design can obviously be tweaked. For example:

For each file in the folder:
check if previously processed (still needs a check list)
if already processed, ignore
if not,
open the file
extract the data
close the file
add the file name to the check list

Each of the steps can be built separately so the overall process can be modified as required.

I've outlined the original approach because I like to be able to see logical break points ... make, or update, the list of files to be processed, process each file on the list, etc. It may or may not be more efficient but, at least, you know where you're up to.

Not a five minute job but relatively straightforward.

Regards

[uncleslinky]

Hi there,

Thanks for the reply. The process you have laid out is exactly what I would want to happen yes. The problem I am faced with is that I have no idea how to accomplish this in VBA although like you said, if I break it into pieces I might be able to find some answers

thanks

[uncleslinky]

(double post)