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.