Import text files dated after last information imported

[totalnewbie]

Hi all,

Here's the situation. We are sending out a survey and tracking who we send it to. The automated system that sends the survey generates a text file called "surveydd mmm yyyy.txt" every time a survey is sent (with the date being the date the survey is sent).

I have created a macro in Access that imports this kind of text file and I have saved the specifications. That all works perfectly.

What I need help with is the code (macro/Visual Basic) that will decide which files to import.

Essentially, the macro should import any file that has a date in the file name after the date of the last survey sent in the tblSurveysSent (field name SurveyDate). As long as the date in the text file name is after the date of the last record (most recent record) in tblSurveysSent, I would like to import that text file (or more than one text file).

How do I do this? If there's any other information that would be helpful to provide, please let me know.

Thank you very much.

[ChipR]

I just happened to be reading TheScripts Tip of the Week # 44 - Microsoft Scripting Runtime #2 by Adezii today, and it seems like you could use it.
Although I didn't see it mentioned in that post, the SystemFileObject has a Files collection that you can use to get all the filenames in a folder. There is even some sample code in MSDN Developer Network Library.

[NeoPa]

There is a Dir() function in standard VBA which gives the same functionality Chip (at least as much as required here).

I would start by setting a Date/Time variable to the date after which files you are interested in.

Use Dir() (or similar) to get a list of matching files.

Go through each filename and extract the date portion, convert that into a Date/Time and compare with the previous variable (date you're interested in). If it is after that, then process the file. Otherwise move on to check the next.