Import CSV with wildcard?

[option]

Hey guys,

I'm trying to import a series of .csv files into a table in my database daily. The only issue I'm having (so far!) is that my file names change daily, and it'd be pretty silly to go in and change the code everyday just to run the process. My file name would be something like "20090224_option.csv", which is todays date_my name. The date AND the name are going to change based on when the file was created and who created it. I have appx 35-40 csv files to import daily, all created by a different user, so my wildcard would obviously be the user name. Is there any way to acomplish this in VBA? Here is my code so far:

Private Sub Inser()
Dim Dest As String
Dim fiPat As String

Dest = "tblMainStorage"
fiPat = "C:\importme\20090224_*.csv"

DoCmd.TransferText acImportDelim, "", "tblMainStorage", fiPat, True, ""
End Sub

 

[option]

Better idea...Is there a way to look in a directory for all files of a certain extension? If that's possible, maybe I could just do that and import the files where the first 8 digits are todays date?

Edit: Found this thread and ghudsons post is exactly what I needed (with minor updating ofcourse)
http://www.access-programmers.co.uk/forums/showthread.php?t=84053&highlight=import+file+wildcard

 

[DCrake]

Here is another example that uses filters and extensions

 

[gemma-the-husky]

use a file picker to select the files interactively