How to dynamically name an export file in Access

I recently had a situation where I needed to archive exported files produced by Access (that were imported into another downstream system) for debugging purposes. In order to archive all the files into the same folder, all the files obviously need to have different names so they don’t overwrite each other. Adding the date to the file name is a pretty good way of going about renaming the files as it guarantees uniqueness, and we’d like to have that information readily visible anyway. The exports were being kicked off by a macro, so the archive files needed to be created as part of this macro. To make this happen we’ll need to do the following:

1. Create a VBA Module that contains a function to create our dynamic name, and save the export file to a specified location.

2. Modify/Create a Macro that will kick off our VBA Module.

These instructions are for Access 2010, however I originally did this in Access 2003. Aside from the subtle differences of where items are located in the menu etc. I can verify that this method will work for both 2003 and 2010.

So let’s get to it.

1.  First thing we want to do is create a new Module in Access. Click on the “Create” tab at the top of the Access window. There should be a “Macros & Code” section available once you’re on this tab. Click “Module” (highlighted in yellow below) to bring up the “Microsoft Visual Basic for Applications” window.

2.  Once the editor opens up it should automatically have created a Module named “Module 1” and opened up a code editor window for the module that’s blank aside from the text “Option Compare Database”. We want to add the following code directly below that text:

Public Function DynamicFileNameExampleFunction()

Dim Current_Date As String

Dim File_Name As String

Current_Date = Format$(Now(), “yyyy\-mm\-dd hhmmssAMPM”)

File_Name = “Z:/ExampleFolder/ExampleExport-” + Current_Date + “.xls”

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, “TestTable“, [File_Name]

End Function

There are a few things you’ll need to modify in the code to fit your particular situation. Use the highlighted colors in the code above and the key below to modify your code as needed.

· The name of the view or table you want to call for your export file.

· The location/path you want to save your export file at. (This path must exist. It will not auto create a folder that doesn’t exist.)

· The file name you want your export saved as. (The date and time will be added after this.)

Once completed your editor should look like this:

3.  Save the module, however DO NOT name the Module the same name as the function (DynamicFileNameExampleFunction in our example). Naming the Function and the Module the same name will cause your macro call to fail later on. I’m going to name our Module “TestModule”.

Once the Module is saved, close the VBA Module editor so that you’re back on the main Access screen. We should now see our new Module in the Objects window as seen below.

3.  All that’s left to do now is create/edit our macro to call our function in the VBA Module. The create Macro button was directly to the left of our create Module button in Step 1. Once the Macro designer is up you’ll want to select “Run Code” for the action, and then type in our function name “DynamicFileNameExampleFunction()” as the code to run. Once completed, your Macro should look like the screen shot below.

5. Now just save your Macro and double click on it to run it. Each time your macro is run, you should create a new export file with the date and time appended to the name. I clicked mine 3 times in a row just to generate a few examples.

Not too difficult, but while I was researching how to do this I came across a lot of people needing a similar solution. Hope this helps out.