Pulling data from multiple files into holding file

Hey folks, just changed jobs and really new to this kinda thing, so please bear this in mind.
Have tried for a couple of weeks to sort this problem out with my very basic macro / VB skills but actually its melting my head. I've ended up more confused than I was at the beginning.

Here's the problem :

I receive xls based survey questionnaires via e-mail, detach the standardised xls file and save onto a directiry on my c: drive called C:/processing

Each month I create a different sub directory ie C:/processing/feb,
and within each month sub directory I have the names of the person that the survey relates to ie C:/processing/Feb/Joe Bloggs

Now I can receive anything from 10 to 20 surveys per person and at the end of the month I have to process them.

I can receive up to 30 separate persons surveys each with 10 -20 responses. ie 300 - 600 files in a months directory.

So what I'll generally have is :

C:/processing/feb/Joe Bloggs - 15 xls returns
C:/processing/feb/Arthur Trout - 22 xls returns
C:/processing/feb/Sam Fox - 19 xls returns

What I then need to do is open, each of the individuals returned xls files and then extract certain data from them into a holding file which I'll name

C:/processing/feb/Joe Bloggs/holding.xls
or
C:/processing/feb/Sam Fox/holding.xls

Because the directory and name changes every time, then I need a solution that I can select the files in the directory that I want to process. The number of xls files inside that directory will vary.

Once the files are selected, then I need to pull the data from the first xls workbook (sheet 1)cells D4, D5, D6 and K19 to AB19 inclusive.
This data is then copy / pasted / transferred / imported into the holding.xls worksheet( sheet 1) starting at cell A1 thru to A21. The 1st 3 cells (A1,A2 and A3) are text "appraisee", "appraiser" and "reporting relationship" (ie boss, peer etc), the next 18 cells are numerical values.

The second workbook in the directory is processed and extracts as above and copy, pastes into holding.xls worksheet( sheet 1) starting at cell B1 to B21.

Then the third to C1 - C21
Then the 4th to D1 - D21
then the fifth etc until all files in the directory have have been processed.

What I'm looking for is a way to automate this process, preferably by selecting and extracting the data without launching or opening the file - ie read closed files. Or by creating a macro button within the holding.xls workbook that gives the option to selects the directory, the files and then transfers the data.

There is mibbe even a better way to do this? I can't think anymore, my head is fried.

Can anyone help me out here?

Huge thanks in advance.

Anyone help here?

Hi
save this file into a folder where you extract data from. Click the button to run the macro. It lists all files in that folder in col A and D4 to D6 in col B to D and K19 to Ab19 in E to V. Test it and let me know how you want it improved.
Ravi

Hi Ravi,

I have just D/L'd your darkfish file as I am trying to replicate some of the functionality.

I would like to be able to collect data, on sheet15 (bulkload) b2:w8, from all the files within a directory and combine it on a new sheet.

I have looked at the macro and tried to work out how you achieved this but as I have virtually no programming experience I have to admit I am utterly baffled!

Would it be possible for you to modify your darkfish file to collect data from files within a folder, ignoring blank rows between B2:W8 on sheet15?

Any help would be greatly appreciated!

Thanks
Rev.

Hi Ravi,

The modified macro works brilliantly!

Thanks for all of your help with this it will be really useful!

Rev.