Excel Vba Save a Copy of Workbook with TimeStamp

Create Excel file copy with Date & Timestamp in Vba

In Vba we can access files & create new files.

While doing such file operations, You may have to save a data file with different names every time the macro is executed. In such cases, if the file name can be appended with a unique identifier – such as  number incremented for every run.

Or You can append data + time at end of the filename.

This will ensure that file gets a unique name every time it is saved.

The main function used in here is: “Thisworkbook.SaveCopyAs (‘filename’)”

VBA code to Add Timestamp to File

There is a tricky part in adding timestamp to filename.

Before adding the timestamp to file, You should first split the filename and its extension (.xlsx, .docx, .txt etc.,) The below macro does this part as well.

Lets get the code now.

Sub SaveFileWithTimeStamp()
    'Code from Officetricks
    'Define variable fields
    Dim sFolderPath As String
    Dim sFileName As String
    Dim sFileExt As String
    Dim sNewFileName As String
    
    'Set Time Stamp
    Dim sDateTimeStamp As String
    sDateTimeStamp = VBA.Format(VBA.Now, "_dd_mmm_yy_HH_MM_SS_AM/PM")
    
    'Get File Folder Path
    sFolderPath = ThisWorkbook.Path & "\"
    
    'Get Document File name
    sFileName = ThisWorkbook.Name
    
    'Split File Name & Extention
    sFileExt = VBA.Mid(sFileName, VBA.InStrRev(sFileName, ".", , vbTextCompare))
    sNewFileName = VBA.Replace(sFileName, sFileExt, "", , , vbTextCompare)
    
    'Add Date-Time Stamp to New File Name
    sNewFileName = sFolderPath & sNewFileName & sDateTimeStamp & sFileExt
        
    'Save the File Now
    ThisWorkbook.SaveCopyAs sNewFileName
End Sub

You could see that first the file is split into filename + extension. Then timestamp is inserted between them. Then the file is saved to new file.

Also note the the date + timestamp can be customized as per your need. In this above example it is given in dd_mmm_yy format.

You can also have it in “mm_dd_yyyy” or any applicable format of your choice.

The main vba command used to achieve this is vba.format