Feb 24, 2014

When creating macros in Excel (or another Office application), you often want to have the macro save the file of results as part of the macro. There are two main issues with saving files: where to save it (which isn't a problem with a macro because you can always write code to make sure the file gets saved in the right place), but more importantly, what to call it. If we start recording, perform the Save As action, and then stop recording, we'll have some code that looks like this:

The first line, ChDir "C:\Temp", changes the current folder that the Open/Save As menus start in when you open those menus. The second line, which is the actual Save As command, records the whole file path anyway. So, we could actually delete the first line of code, and as you can see, getting the right place isn't a problem. The problem occurs when we run the macro:

Since the file already exists, the macro has to pause to ask the user whether or not they want to overwrite it. There are two problems with this.

Problem #1: 

I really don’t want the user to have to interact with the macro as it slows the whole process down. Even worse, if the user clicks on 'Cancel' then the macro stops halfway through with an error message. A workaround would be to use a VBA command that suppresses error messages such as:

• Application.DisplayAlerts = False
• Or more specifically: Application.AlertBeforeOverwriting = False

We could put that at the top of the macro and then add either of the following at the bottom of the macro.

• Application.DisplayAlerts = True, or
• Application.AlertBeforeOverwriting = True

Turn off the alert, do our work, then turn it back on again at the end. However, this doesn't solve the second problem. What is the second problem I hear you ask?

Problem #2:

Do we really want a macro that overwrites (destroys) a file automatically? That sounds (and is) dangerous! When making a macro, I like to be able to say at the end, my macro CAN’T do any damage. It can fail, or not do the thing it’s meant to, but it can’t do damage to existing files. I want this macro to ALWAYS create a new file and never overwrite an existing one. So we need to be able to save the file with a unique name, a name that’s different for each time the macro gets run.

I could ask the user to give me a file name, but that defeats the purpose of using the macro in the first place. A macro is meant to automate a process. A much better idea is to use a code or sequence that is never the same twice, such as the current time and date.

In Excel, there is a function called =TODAY(), which gives the current date, and =NOW(), which gives the current date and the current time. Bingo - NOW sounds like what we want.

If you put this function into the immediate window in the VBA editor,  it should result to something like this:

Using the 'NOW' function by itself won't work as file names don’t like slashes or colons. Hence, we need a way to write the code so that it gets rid of those bad characters. There’s another function in Excel we could use called =TEXT. 'TEXT' has two parts inside the brackets,

1. A value - which is the date and time for us, and
2. A text_format - which is the format we want to see the value in

The text_format is the same series of codes that you see in the Format Cells menu, for example, “dd-mm-yyyy”. You can click on the 'Help' menu for the 'TEXT' function to see a full list of examples. So inputting =TEXT(NOW(),"yyyymmdd") into a cell in Excel should result in the current year, month, and day with no spaces. For me, it gives 20140204. But does VBA have the same thing? I’m going to give a classic answer: ‘Yes but…’ It does, but it’s called 'FORMAT.' It works exactly the same, but the only difference is its name. Inputting ?FORMAT(NOW(),"yyyymmdd") into the immediate VBA window will give the same result. It’s almost there! A user might run my macro more than once in a day, so we'll need to add a time component. If we adjust the code to FORMAT(DATE(),"yyyymmddhhmmss"), it will work perfectly. Most macros take at least a second to run so we should never get the same date/time code more than once.

The Final Step

Now, we need to put this back into the SaveAs macro. Note that the 'FORMAT' function results with text, so all we need to do is concatenate it on the end of the file name parameter.

Note that I moved the FileFormat parameter down to the next line to make it easier to read, but make sure to leave the space and underscore at the end of the first line. This way, VBA knows the code continues on the next line. Here’s the code for you to try out:

Sub SaveUnique() ' ' Save a file with a unique name ' ActiveWorkbook.SaveAs Filename:="C:\Temp\Book" & Format(Now(), "yyyymmddhhmmss") & ".xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False End Sub

Now it's your turn. Have a go and run this code in Excel VBA and see if it'll work for you. If you're interested in learning more about macros and Excel VBA, I recommend that you take a look at New Horizons' Excel VBA courses.