Excel vba Add Date Time Stamp to File Name

Save File with Date Time Stamp

In Excel vba we can create new file or save the current workbook with different name.

In that case, sometimes we add date, time or both at end of file. This way, the file names will be unique & also the file versioning can be maintained easily.

Use this code to add such time-stamp or date-stamp to the file.

Add Date Time to File Name before Extension

Before editing filename, You need to do some preparatory tasks.

1. Parse File name & its extension
2. Get today Date & Time
3. Format as per use preference
4. Add formatted Date-Time-Stamp before file extension.
5. Save the file.

Sub add_date_time_stamp_to_file()
    'Code from officetricks.com
    Dim sFileName As String, sFileExtension As String
    Dim sDateTimeFormat As String
    
    'Define Date Time Stamp Format
    sDateTimeFormat = "dd-mmm-yyyy-HHMMSS"
    
    'Get File Name with Path
    sFileName = ThisWorkbook.FullName
    
    'Get File Extention
    sFileExtension = VBA.Right(sFileName, VBA.Len(sFileName) - VBA.InStrRev(sFileName, "."))
    
    'Remove Extension from File name
    sFileName = VBA.Left(sFileName, VBA.InStrRev(sFileName, ".") - 1)
    
    'Add Date-Time Stamp to File name
    sFileName = sFileName & VBA.Format(VBA.Now, sDateTimeFormat) & "." & sFileExtension
    
    'Save the File with Date & Time Stamp
    ThisWorkbook.SaveAs sFileName
End Sub

What does the above code actually do?

It takes the current workbook full name along with path. Then go through steps explained above the code.

Save the workbook with different name each time the code is executed.

This macro code can be mapped to a keyboard shortcut & used whenever the file needs an intermediate save.

It is pretty much a cool code. Isn’t it. Saves a lot of time, so that Your work is not lost.