VBA Transfer Text with non static file name.

[apank]

Using VBA, is there a way to transfer text a table or query and have the saved file name be some text followed by todays date (example"newfile.11.17.08.txt).

Also can that file then be winzipped and emailed using VBA? I know how to email it, but I want to zip it first.

Thank you for any help with this.

[Stewart Ross]

Hi, and Welcome to Bytes!

If you are using the DoCmd.TransferText method to output to a text file you will be supplying the filename as a string. You can always add the date to that string before you specify the file extension, using the Format function:

DoCmd.TransferText acExportDelim, , "YourTable", "C:\yourfilename " & format(Date(), "yymmdd") & ".txt"

This adds the date year-first - 081118 for 18 November 08. You can change this to whatever suits you.

There is no direct way to zip your file from VBA code. Attachments can be e-mailed from VBA, but there is no interface to products like WinZip.

-Stewart

[apank]

Thank you for your help Stewart.