Selecting files based on date range

Hi all -

I have a code which will copy the first page of all Excel files within a specific directory to a new workbook then do a search for a keyword and return the results. The problem is the the files in the source directory grow every day. I need to incorporate some sort of date range function which will let me choose files created between specific dates. Do you have any suggestions/knowledge/code that you think would help?

Thanks
FIS Mitch

FISMitch,

Consider using the FileDateTime function. This function returns the date and time that a file was created or modified. Here is a link that describes how to use the function: http://msdn.microsoft.com/en-us/libr...(v=vs.90).aspx

What you can do is write an if-then statement into your current procedure that uses the output of the FileDateTime fuction for each Excel file in your directory. If the date returned is between the two dates you specify, then proceed with the copy, paste, and search actions. If it is not in between these two dates, then do nothing.
______________________
[1] If this reply was helpful, click the star to the left.
[2] If this reply answered your question, mark this thread as [SOLVED].

Thank you xladept -

Your changed did get me further but I am finding an issue where S and E are showing = Empty. When this happens the code makes a copy of the first sheet of the workbook I have this code in. I attempted a couple different date formats to see if that might be the issue but did not have any luck.

Thanks again xladept -

The variables are now working but the code is making copies of the first sheet of the workbook the code is in. Any addiitional suggestions?

Thanks again -
FISMitch

FISMitch,

I suspect that is happening because the workbook that contains the code is in the same directory as the files the code is operating on. Would you be okay with keeping "ThisWorkbook" in a different folder? From the looks of it, this should be fine, since your code above spells out the directory that VBA should look into, i.e., H:\BSD MM Search\Test.

Hope this helps.
__________________________
[1] If this reply was helpful, click the star to the left.
[2] If this reply answered your question, mark this thread as [SOLVED].

xladept and Leith -

I truly appreciate your assistance with my questions. The code modifications have stopped recreating the first worksheet of the workbook the code is, however it is not opening/closing/copying from the workbooks that fall into the date range I have specified in A1 (my start date) and B1 (my end date). I can only think it is the date format I am entering. I checked the regional settings and the date timestamp for the files I need to import and tried several different formats but that did not help. Do you know what date format I need to use in cells A1 and B1 that act as my range. Also, I noticed when I placed a date in cell B1 that was outside of the date range of files that are in the \test directory I got an error Run time error 438 on the oFile.Open line.

Thanks again -

Originally Posted by xladept

It's working for me?

Please Login or Register  to view this content.

BTW - Why is oFso being created??

Thanks again xladept. I have tried and it is still not working properly. What is the date format you are entering in A1 and B1?