Save File As Same base name, but add the date to the beginning

[gkisystems]

I'm looking for a macro that is stored in my personal Excel file so that it can be deployed at any time with any of my files. I'm trying to get the macro to take the active file and then save it, but add the date in front of the base name. For example, say I'm working in a file called test.xls. When I run the macro, I want it to save as: 2014.06.26 - test.xlsm. If I open the file the next day and run the macro again, I want it to save as 2014.06.27 - test.xlsm. I'm not sure this is possible to do because the macro would need to know if the file I'm working in already has the file name at the beginning or not (whether the file is currently saved as "test" or "YYYY.MM.DD - test" for example.) In the event it is impossible to design a macro that works dynamically like this, then I would prefer to just assume the date is already at the beginning of the file name, so the macro would automatically strip out the first 9 characters "YYYY.MM.DD" and replace them with the current date.

If I run the macro again on the same day, I want it to error out with some sort of message saying "Your save action has been canceled because the file 2014.06.27 - test.xlsm already exists!" and then ask the question would you like to save the file as "2014.06.27 - test_version002.xlsm?" If the macro is then run a 3rd time that day, it would error out and ask the user to save as "2014.06.27 - test_version003.xlsm and so forth. If it's not possible to have the macro add versions to the end of the file name, then I'd prefer that it just errors out and stops.


I would like the macro to save the new file in the same folder that it already sits in. For some reason, some other macros I've attempted to try on my own have been saving the file to my Libraries\Documents folder and I do not want them saved there.

Any help is appreciated!

 

[Andrew Poulsom]

You can try:

With ActiveWorkbook
    .SaveAs Filename:=.Path & Application.PathSeparator & Format(Date, "yyyymmdd") & .Name
End With

You can use the Dir function to check if it already exists.

 

[racefankevin]

Ok I am a very beginner VBA writer. I am looking basically for the same function. However, I want the date at the end. This is the current script I have
ActiveWorkbook.SaveAs Filename:= _ "H:\My Documents\kevin.xlsx", FileFormat:= _xlOpenXMLWorkbook, CreateBackup:=False Then I would like to add the current date to it. How would it be written? Thanks in advance.

Kevin

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

    ActiveWorkbook.SaveAs Filename:= _
        "H:\My Documents\kevin" & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False