Use VBA to extract data from multiple files

Hello everyone,

So I am stuck and don't know how to do a task I need. I am not very good with VBA so I am hoping to get some help here. This is what I have:

I have 350 excel files all in one folder some with one worksheet and some with two. They all have the same formatting and look identical except for the variable numbers of course. I need to create a new document which will have only specific cells of the other files. It should look like this:

A B C
1 A1 A4 SUM(C7:O54)
2 A1 A4 SUM(C7:O54)

Obviously each row is data from a different file.

Any help? thank you so much!

Hi

DIR is your friend for getting the filenames. You'll also need a number of loops. Something like this:

Please Login or Register  to view this content.

that should get you started.

Cheers

Mat

tigeravatar,

First of all, you are awesome for taking the time to help me out with all of this code. You are one amazing person!

I am having a problem with the code though, once I run the VBA and it goes through what seems to be all of the files it gives me the following error and does not write anything on the new file but the headers:

Error: 1004
Method 'Open' of object 'Workbooks' failed

Why do you think that is?

Once again, thank you so much for all of your help!
-JCHC

tigeravatar,

Thank you very much, I found out yesterday that one of the files was corrupted. That is now fixed and it ran very smoothly. You have no idea how much help this code has been and how much it will help in the future. You guys are awesome!

JCHC