Help with macro to backup file t0 same folder and append the current date & time.

[scatki]

I’m struggling to write a macro that will back up the current file into the same folder and append the current date & time.

For example, I want to back up a file named Simple.xlsx in the exact same folder and name the file Simple 032522 101546.xlsx.

I would be extremely grateful if you can help.

Thanks
-Scott

 

[Joe4]

Welcome to the Board!

Where do you want this code stored?
If it is to be stored in the file itself, it cannot be saved as an "xlsx" file, as "xlsx" files cannot contain VBA code. It will need to be a "xlsm" or "xlsb" file.
Ot do you want the code in separate workbook, where you select the file that you want backed up?

 

[scatki]

Welcome to the Board!

Where do you want this code stored?
If it is to be stored in the file itself, it cannot be saved as an "xlsx" file, as "xlsx" files cannot contain VBA code. It will need to be a "xlsm" or "xlsb" file.
Ot do you want the code in separate workbook, where you select the file that you want backed up?

Hi Joe, I would like to eventually store in my PERSONAL.XLSB file so that it's always available. I'm just trying to get the formatting of the file name correct and make sure it saves in the same folder. And yes, understand the file needs to be an .XLSM file to store the code for testing.

 

[Joe4]

OK, if you put the following VBA code into your Personal Macro Workbook, then you can run it on any open active workbook.

Sub CreateBackup()
'   Creates backup of active workbook

    Dim fpath As String
    Dim fname As String
    Dim new_fname As String
    
'   Get path and name of current active workbook
    fpath = ActiveWorkbook.Path
    fname = ActiveWorkbook.Name
    
'   Build new file name
    new_fname = Left(fname, InStrRev(fname, ".") - 1) & _
        Format(Now(), " mmddyy hhmmss") & _
        Mid(fname, InStrRev(fname, "."))
        
'   Save file
    ActiveWorkbook.SaveAs fpath & "\" & new_fname
    
'   Notify user of save
    MsgBox "File saved to:" & vbCrLf & fpath & "\" & new_fname, vbOKOnly, "FILE BACKED-UP"
    
End Sub

 

[scatki]

Thank you, Joe! I really appreciate your help on this!

 

[Joe4]

You are welcome.