Linking to varying CSV files in varying directories 1

[AlisonH]

Can anyone help me (a VBA beginner, in case you can't guess)with automating my report generation? What happens is that a directory containing a couple of CSV files is created by our finance system every day and named according to the day, eg Data_File_1_030601 and Data_File_2_030601 in a directory called Data_030601.

At the moment I'm asking anyone who wants to produce a report to copy the relevant data file into the database directory and rename it, then run the report they want. To make it easier to use I'd like to be able to ask them for the date of the file they want, then for it to link to the relevant file in the relevant directory.

I've got an Input box to ask for the date but can't work out how to get the results into DoCmd.TransferText.

Thanks, Alison.

 

[RickSpr]

Construct a string variable containing the file name, using the Format$() function to compose the date part:
strFileName1 = "Data_File_1_" & Format$(strDate, "mmddyy&quot
strFileName2 = "Data_File_2_" & Format$(strDate, "mmddyy&quot
Then just use the file name variable in place of the actual file name in the TransferText call:
DoCmd.TransferText acImportDelim, specName, tableName, strFileName1 Rick Sprague