Open files within date range

[originalJ]

Hello everyone,

I have a set of excel files that I needed opened then a subsequent macro will take place. The files are labeled like so "date Master" (e.g. 05-17-2016 Master). For the most part I do not have gaps on filing system so theoretically by the end of the year I'd have 365 files in that particular folder. Not sure if pertinent to the process but I figured to give out full disclosure.

I'd need to have a macro to open files from last Friday's date through the file that is dated 30 days prior to (if that makes sense ) that would compile the sheets into 1. To further elaborate since I think my description is clear as mud this would be the concept of the process:

If I run the report today, 5/17/2016 or any time this week for that matter, with last friday's date being 5/13/2016, the report the macro would open file 04-16-2016 Master, copies the data into destination sheet, close, then open 04-17-2016 Master,copies the data into destination sheet, close, then open 04-18-2016......until it opens 05-13-2016 Master, copies the data into destination sheet, then closes thus ending the loop

I have bits and pieces of the information that I need but I can seem to put them together to have a cohesive code. Here's what I have so far which is pretty much a dud

Please Login or Register  to view this content.

Thanks for any help!

[dflak]

This is the way I'd do it. I'd also add some checks in the copying code to check if the file exists, just in case there are gaps.

Please Login or Register  to view this content.

[originalJ]

This worked beautifully and exactly how I imagined and needed it to function. Thank you! So I did encounter a file name gap. How would I incorporate that into the macro? I know error handlers exist but no idea how that works.

Thank you!

[dflak]

Here's a simple test to see if a file exists. Wrap your code in an if statement. This function returns TRUE if the file exists and FALSE if it doesn't.

Please Login or Register  to view this content.