Pulling data from files by part of file name

I have forms by differnet departments each day.

The files are saved as the department's name then date

Byrd 82708.xsl

I need a command button that will pull data from three cells in each of these forms.

The master list will not be in the same folder as the deparment forms, the cells are E20, f20, f25

I have a text box were the user will input the date, by this date I would like all forms with this date in that folder to have their data pulled from those three cells and returned in master list.

Let me see if I understand what you're saying ...

- You have a master list in directory "A".
- There are depatment forms - separate ones for each dept and day - where the file names are appended with the date. These are in directory "B".
- In the master list, being an excel file, you pop up an input box, the user enters a date, and you want to find all the department files for that date and extract from each one the data in cells E20, F20 & F25.
- This data is appended (?) to the master list in 3 columns.
- Presumably, you would have at least 5 columns in the master list ... one each for: department, date, and the 3 extracted bits of data?

If this is right ... what happens if data for the date the user inputs is already in the master sheet? Delete & replace? Add duplicate? Ignore & advise the data is already there?

Hi james580 ... here are two sub routines; paste them into the workbook that contains your master sheet. You may need to move it into whichever file is calling this and create a master sheet for each date, but i'll leave that to you. Also attached are the test files I created ..

Please Login or Register  to view this content.

Hope that helps. MM.

Hi james580 - if you've seen the post below, you might be trying to modify it to cater for how I think you've got it structured, which is that you have a "control" book which invokes the process, then separate master books in their own directory (i've assumed they're labelled "Master - 05 Oct 08" and "Master - 06 Oct 08" and so on) and the department files in their own directory (i've assumed they're labelled "dept A - 05 Oct 08" and "dept B - 05 Oct 08" and so on).

SO - i've reworked the code below so it now runs in this structure; it's a little rushed i'm afraid (end of day!), but it does run and i've trapped most of the common errors you might come across.

Anway - this may be useful if you're looking to extend the code as i've assumed - new "control" and test files are attached as well.

Put these in your workbook object:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

And then something to call it (from an input box in your case - the button on w/sheet 1 in my example) - so for me this is in w/sheet 1:

Please Login or Register  to view this content.

Please read the notes / comments carefully .. and if you step through the code, I'm sure you'll see what's happening