Rename export name

[CCIDBMNG]

I have a macro set up that e-mails a spreadsheet which is based on one of my queries. When you do this access automatically names the spreadsheet the name of the query. Is there any way to change the name of the spreadsheet to be the name plus today's date? Any help is greatly appreciated.

Thanks.

 

[dcx693]

Yes. I actually never work with macros, but I thought I'd give this question a shot. In the TransferSpreadsheet macro action, one of the parameters is Filename. In that field, I placed this expression:
="C:\test" & Format(Now(),"mmddyyyy") & ".xls"

My spreadsheet got called test06102003.xls. Do something similar in your macro.

 

[CCIDBMNG]

Yes that works with the Transferspreadsheet option but I am using the SendObject option which doesn't save the spreadsheet to the hard drive but automatically sends it to an e-mail. Thanks for trying though.

 

[Jon K]

I often use a workaround.

I type the desired file name (without the extension .xls) in a text box txtFileName on a form and run the following code from the On Click Event of a command button on the form:-

DoCmd.CopyObject , Me.txtFileName, acQuery, "queryName"
      
DoCmd.SendObject acSendQuery, Me.txtFileName, acFormatXLS, _
      "me123@hotmail.com", , , "subject text here", , False
      
DoCmd.DeleteObject acQuery, Me.txtFileName

The code just makes a new copy of the query under the desired name, sends the email, then deletes the query copy.

 

[CCIDBMNG]

Thanks so much Jon that worked perfectly.