Automatically Save an Excel File with the Date and Time Using VBA
Question details
The user needs to save an Excel workbook automatically with the current date and time added to its filename when a custom macro runs.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A timestamped copy of a workbook is needed each time the user runs a save macro, optionally from a shortcut or button.
- Observed behavior
- The workbook is saved using a custom filename that includes the current date and time, and SaveAs changes the workbook's filename and location.
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) before adding the code. Confirm that the workbook has already been saved to a folder, because ThisWorkbook.Path is empty until the file has a location.
Use a VBA macro to save the workbook with a timestamped filename
Use this method when each macro run should create a dated and timed workbook file in the same folder as the current workbook.
The macro uses ThisWorkbook.Path and the Format function to build a filename such as YourFileName_20250130_143025.xlsm. The SaveAs command changes the active workbook's name and location, so use SaveCopyAs instead if you need to keep the current workbook name open.
In Excel, press Alt+F11 to open the Visual Basic Editor, select Insert > Module, and create a standard module rather than placing the code in a worksheet module.
Paste this code into the module: Sub SaveWithTime(): Dim p As String: p = ThisWorkbook.Path & Application.PathSeparator & "YourFileName_" & Format(Now, "yyyymmdd_hhnnss") & ".xlsm": ThisWorkbook.SaveAs Filename:=p, FileFormat:=xlOpenXMLWorkbookMacroEnabled: End Sub. Replace YourFileName_ with the filename prefix you want to use.
Return to Excel, use File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and enable macros if Excel displays a security warning.
Press Alt+F8, select SaveWithTime, and click Run. The workbook is saved in its current folder with the date and time appended to its filename.
Open the Macro dialog with Alt+F8, select SaveWithTime, choose Options to assign a shortcut, or use Developer > Insert to add a button and assign the macro.
Save a timestamped copy without renaming the open workbook
Use this alternative when the current workbook should remain open under its original name after the macro creates a timestamped file.
Create timestamped spreadsheet files with WPS Office macros
WPS Office provides a familiar spreadsheet interface for working with macro-enabled Excel files. Open the workbook, use the VBA or macro tools available in your WPS edition, and save the result in a compatible .xlsm format with a timestamped filename.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheets, and open the Excel workbook you want to timestamp.
- 2. Open the macro tools: Use the Developer tab or the available macro/VBA entry point in your WPS edition to create or edit the save macro.
- 3. Add the timestamp filename logic: Create a macro that combines the workbook path, a filename prefix, and Format(Now, "yyyymmdd_hhnnss") before saving the file as .xlsm.
- 4. Run and verify the save: Run the macro from the macro dialog or assigned button, then check the workbook folder for the newly timestamped file.
Frequently Asked Questions
Why does the timestamped macro change my workbook's name?
ThisWorkbook.SaveAs saves the open workbook under the new timestamped filename, so the workbook window uses that new name. Use ThisWorkbook.SaveCopyAs when you want to create a copy without renaming the workbook that remains open.
Where is the timestamped Excel file saved?
The example saves the file in ThisWorkbook.Path, which is the folder containing the current workbook. The workbook must already have been saved once; otherwise, ThisWorkbook.Path is empty and the generated path may be invalid.
What does hhnnss mean in a VBA date format?
In VBA, hh is the hour, nn is the minute, and ss is the second. Using yyyymmdd_hhnnss creates a sortable filename such as YourFileName_20250130_143025.xlsm.
Can I run the save macro from a button or keyboard shortcut?
Yes. Press Alt+F8, select the macro, and choose Options to assign a shortcut. You can also use Developer > Insert to add a form-control button and assign the macro to it.
