How to save an Excel file with today’s date using VBA

Regular Excel reports benefit from having the date saved in the filename, learn how to automate this process through VBA in this how to guide…

 

How to VBA date in Excel filename 0

There are many occasions when you need to add a date to a file name when saving, for example if you are producing a daily sales report it would be prudent to add the date of the file in the file name to make it easy to store and track the latest report.

Of course one way is to do a FILE -> SAVE AS and then specify your date in the file name but we can do one better than that and use VBA to automate this process, here’s how:

Saving the Excel workbook using VBA can be achieved using the script:

ActiveWorkbook.Save

And we can specify an exact path and filename by using the following:

ActiveWorkbook.SaveAs ([Filename])

Where filename is replaced by the file-path and name of the file we wish to save.  So if we want to save the file as “Monthly Sales Report” in the path “C:\My Documents” we would use the code:

ActiveWorkbook.SaveAs (“C:\My Documents\Monthly Sales Report.xlsx”)

Note the .xlsx at the end of the filename, this tells Excel what format to save the file in so make sure it is relevant to the version, or type of Excel file that you need to save.

Now we know how to save the file in a defined location with a specific name, how do we automate a date? 

We use the system clock, which is obtained by the function Now().

However, we need to do a little formatting as specifying Now() returns today’s date and time with all those special characters that are not allowed in a excel file name (colons etc.).

Formatting today’s date into one we can use is achieved using the Format function. So to format today’s date into DD-MMM-YY format we just use:

Format( Now(), “DD-MMM-YY”)

And combining it into our original VBA script is done like so:

How to VBA date in Excel filename

If we run the above script on the 1st January 2012 the file would be saved in the “C:\My Documents” path with the name “Monthly Sales Report 01-Jan-2012.xlsx”

You can take this a step further if you want and use today’s date as a reference for calculating a different date, for example if you wanted to save the file with the previous days date you would just change the VBA script to:

ActiveWorkbook.SaveAs (“C:\My Documents\Monthly Sales Report  “ & Format(Now() – 1, “DD-MMM-YYYY”) & “.xlsx”)

And there you have it, a nice and easy piece of code to implement that can enhance your existing macros, or give you a good start point for when you create your next one.  Why not start implementing this into some code today and save yourself the hassle of having to rename a file with a date ever again!

Keep Excelling,

Leave a Comment