Creating a variable to change the name of an export file

[fresh]

I have a database that I periodically export to a text file for a Word mail
merge. I have a query that finds all of the records with a null export date
and a query that updates the export date to now().

I have a macro that runs the first query, uses TransferText to send it to a
file, then runs the update query. However, the file name in TransferText is
"hard wired" so whatever was there before gets written over.

Is there a way to create within the macro an environmental variable for the
file name based on date/time so that I can keep these export files?

 

[Joe Fallon]

I would use VBA code.
The macro may accept something like this:

MyExportPath = myDir & Format(Now(), "yyyyMMddHHmmss") & ".txt"