How to save an Excel filename with timestamp?
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 (replace the file name 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 followed by the original file name instead of replacing it, please apply the below VBA code.
VBA code: Save an Excel filename with timestamp (Insert timestamp followed by the 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:
- How to save a worksheet as PDF file and email it as an attachment through Outlook?
- How to use Save As function to automatically overwriting existing file in Excel?
- How to save, export multiple/all sheets to separate csv or text files in Excel?
- How to disable or do not allow Save & Save As options in Excel?
- How to disable workbook save but only allow save as in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
