Export File Name with Today's Date

[sombiatz]

Hi All,

I have been working with my boss on an access database that compiles a list of daily charges and creates an excel document in a shared folder. Currently, I am going into the database each day and running the macro my boss created. The file is created with the following name: MTProfessionalCharges.xlsx. I then have to go into the shared folder and change the excel file name with today's date. For example, today's file (6/10/14) would be renamed as "2014.06.10MTProfessionalCharges.xlsx." Is there a way alter the macro so the file name does not need to be changed? Attached is how the macro currently looks in Access 2010. I do not have a programming background so please simplify the solution as much as possible.

Thanks!
Martha

[ranman256]

You would need to change the macro and add code.

instead of running the ACTION transferspreadsheet, you must build a custom function that the macro can run..

public function ExportFile()
dim vFile

vFile = "c:\myfile\folder\"  & format(date(),"yyyy.mm.dd") & "MTprofessionalCharges.xlsx"

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "BaseChargesQueryD", vFile, True
end function

the macro would RUNCODE ExportFile()

[sombiatz]

Thanks for the help. I tried to do what you said but it didn't work due to my lack of coding knowledge. I tried editing the macro to include a Run Code action. I didn't know how to go about creating the custom function so I opened the macro in Visual Basic and just copied and pasted the code you provided above. When I tried running the macro, I received an error message stating it could not find the function ExportFile().