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