vba to combine data from multiple workbooks

[smlevron]

I have a daily workbook that consists of three sheets. One of the sheets is a productivity log for that day. The workbook is saved via an existing script to create the names using a consistant format which is dd-mmmm-yyyy filename.xlms. At the end of every month, the data from these workbooks needs to be brought together in a single workbook where the data can be fully analyzed. I have used various means of realating to different workbook data, but I can not quite figure this one out since the file name is changing day to day and month to month.

Basically what I would like to do (I think) is have a vba script that would get a starting poing from the destination workbook, cell C3 on worksheet monthly totals. The starting point would be the 1st of a given month. The vba script would then go out and get the data from the other workbooks and post to the new workbook. This all makes sense in my head, but I have been at this for a few hours on and off, and I have yet to figure out what seems to be a simple solution.

Any help on this idea or a different solution to the same problem would be awesome. The help is much appreciated.

Thanks!!

 

[b.downey]

It sounds like you want to query a Directory looking for any occurances of "*-MMMM-YYYY*.xlms" where the Month and Year are part of the query. Each of the files that meet the condition would be accessed and the data would copied to the destination sheet.

If this is the case, Could you provide an example of what the directory looks like?

 

[smlevron]

Thanks for the reply and sorry for the delayed response, I have been out of the office.

I don't really know how I could post what the directory looks like, the best I can think to do is give you a more detailed description.

The directory is stored on a shared network drive (G in a public folder. The destination folders are stored here and would have the following address:
G:\public\bulk\bulk files.

Within the bulk files folder we store several different types of files which are all stored with a file name that begins with the day it was created:
Examples: 11-JUL-2012 FILE 1.XTSM, 11-JUL-2012 FILE 2.XTSM, 11-JUL-2012 REPORT. XTSM

The "file" files are indefinite in number there may be 1 or 50 of them, the Report file will only be one for each given day. The report file is a macro enabled template that automatically creates the file with a specified name format, and every report is identical in layout. The report basically is a user entered collection of certain data from the "file" files that are created during each day.

Hope that all makes sense, and that you can help me towards a solution.

I'm also open to a Macro that could be installed into the save feature of the daily report file, where when the save macro is activated that it would push the data to the larger JULY-2012 MONTHLY REPORT.XTSM. Basically any means of gathering this data to a central file for analysis.

Again, Thanks!