Have you ever tried to save an Excel file with current timestamp? This article will show you method to achieve it.
Save an Excel filename with timestamp with VBA code
Save an Excel filename with timestamp with VBA code
You can run the below VBA code to save an Excel filename with timestamp. Please do as follows.
1. In the workbook you need to name it by current timestamp, please press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, please click Insert > Module. Then copy the below VBA code into the Code window. See screenshot:
VBA code: Save an Excel filename with timestamp
Sub SaveAsFilenameWithTimestamp() 'Updated by Extendoffice 20191223 Dim xWb As Workbook Dim xStrDate As String Dim xFileName As Variant Dim xFileDlg As FileDialog Dim i As Variant Application.DisplayAlerts = False Set xWb = ActiveWorkbook xStrDate = Format(Now, "yyyy-mm-dd hh-mm-ss") If Right(xWb.Name, 4) = "xlsm" Then xFileName = Application.GetSaveAsFilename(xStrDate, "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm") Else xFileName = Application.GetSaveAsFilename(xStrDate, "Excel Workbook (*.xlsx),*.xlsx") End If If xFileName = False Then Else xWb.SaveAs (xFileName) End If Application.DisplayAlerts = True End Sub
3. Press the F5 key to run the code. Then a Save As dialog box pops up, you can see the timestamp displaying in the File name box. Please specify a location to save this file, and then click the Save button. See screenshot:
Notes:
1. The new created Excel file will be saved with the name of current timestamp directly.
2. For those already existing Excel file, the original file name will be replaced with the timestamp.
If you only want to add timestamp within the original file name instead of replacing it, please apply the below VBA code.
VBA code: Save an Excel filename with timestamp (Insert timestamp within original file name)
Sub AddTimestampToFileName() 'Updated by Extendoffice 20191223 Dim xWb As Workbook Dim xStr As String Dim xStrOldName As String Dim xStrDate As String Dim xFileName As Variant Dim xFileDlg As FileDialog Dim i As Variant Application.DisplayAlerts = False Set xWb = ActiveWorkbook xStrOldName = xWb.Name xStr = Left(xStrOldName, Len(xStrOldName) - 5) xStrDate = Format(Now, "yyyy-mm-dd hh-mm-ss") If Right(xStrOldName, 4) = "xlsm" Then xFileName = Application.GetSaveAsFilename(xStr & " " & xStrDate, "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm") Else xFileName = Application.GetSaveAsFilename(xStr & " " & xStrDate, "Excel Workbook (*.xlsx),*.xlsx") End If If xFileName = False Then Else xWb.SaveAs (xFileName) End If Application.DisplayAlerts = True End Sub
Related articles:
