Automatically Record the First Save Date in Excel Using VBA
Question details
The user needs a method to automatically record the exact date and time an Excel workbook or template is first saved, without the timestamp changing on future saves.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an Excel template or tracker where establishing and preserving the original document creation or first-save timestamp is required for auditing or tracking purposes.
- Observed behavior
- The goal is to capture the initial save date and time into a specific cell (such as B1) using a VBA script, ensuring this timestamp remains static and unchanged during any subsequent save operations.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macros are enabled in your security settings, otherwise the VBA script will not run.
Use VBA to Capture and Freeze the First Save Date
Utilize the Workbook_BeforeSave event in the ThisWorkbook module to check if a specific cell is empty before writing the save timestamp.
This VBA method automatically intercepts the save action. By checking if the target cell is empty first, the code ensures that the 'Last Save Time' document property is only written once. On all future saves, the cell is ignored, keeping your original timestamp perfectly intact.
Open your Excel workbook and press 'Alt + F11' on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left side of the VBA Editor, look for your workbook's project name and double-click on 'ThisWorkbook'.
Copy and paste the following code into the blank code window: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Sheet1.Range("B1") = "" Then Sheet1.Range("B1") = BuiltinDocumentProperties("Last Save Time") End If End Sub
If you want the timestamp to appear in a different cell or sheet, modify 'Sheet1.Range("B1")' in the code to match your preferred location.
Close the VBA Editor. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown menu.
Run Macros and VBA seamlessly in WPS Spreadsheet
WPS Spreadsheet offers comprehensive support for VBA macros, allowing you to easily automate data entry tasks, track save dates, and manage complex workflows just as you would in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook or template in WPS Spreadsheet.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon menu and click on 'VBA Editor'.
- 3. Open ThisWorkbook: In the Project Explorer pane within the editor, locate your file and double-click on 'ThisWorkbook'.
- 4. Insert the Code: Paste the BeforeSave VBA script into the code module and close the editor.
- 5. Save Your Work: Save your document as a Macro-Enabled Workbook to ensure your new first-save automation is preserved.
Frequently Asked Questions
Why isn't the VBA code writing the date when I save my workbook?
This usually happens if macros are disabled in your Excel Trust Center settings, or if the file was not saved as a Macro-Enabled Workbook (.xlsm). Additionally, verify that the target cell (e.g., B1) is completely empty before you initiate the save.
Can I record every save date instead of just the first one?
Yes. If you want the cell to update every time the document is saved, simply remove the `If Sheet1.Range("B1") = "" Then` and `End If` lines from the VBA code. This will force the macro to overwrite the timestamp cell upon every save.
How do I change the sheet or cell where the date is recorded?
In the provided VBA code, you can easily change `Sheet1.Range("B1")` to match your desired sheet's Code Name and cell reference. For example, changing it to `Sheet2.Range("A5")` will output the first save date into cell A5 of Sheet2.
