Creating File Names with Dates in Code

How to create a file name that includes the date.

Q:  Steve Mason is trying to export data from a query to an Excel worksheet, and save each exported file with today’s date in the file name, for example qrytest.01/01/2004.xls.  How can this be done?

A:  Slashes in the date won’t work — they are illegal characters in a file name.  It’s also a good idea to avoid periods and other punctuation marks (except underscores and spaces) when creating file names in code.  Use the Format function with the date format of your choice (I suggest m-d-yyyy or d-mmm-yyyy).  to create the file name.  Here is some code from the sample database for Access Archon #127 (Creating Excel Time Sheets from Access Data) that saves an Excel workbook with a name that includes the date:

   strSaveName = strDocsPath & “” & strEmployeeName _

      & ” time sheet for week ending ” & Format(dteWeekEnding, _

      “d-mmm-yyyy”) & “.xls”

 

You can use the Date function instead of a date variable to get the current date into the file name.

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.