How to Use a VBA Macro to Save an Excel File with Today's Date in WPS Office
Question details
The user needs a VBA macro that automatically saves an Excel workbook with the current date appended in MMDDYY text format, for example PM Record - Job Number - 032425.xlsm.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user wants to automate daily workbook saves so each file is named with the current date, using a macro-enabled workbook.
- Observed behavior
- The expected result is a macro-enabled .xlsm workbook saved with a filename ending in today's date as text, such as PM Record - Job Number - 032425.xlsm.
Make sure the workbook is saved as a macro-enabled file (.xlsm), and confirm that the Developer tab is available in WPS Spreadsheets so you can open the Visual Basic Editor.
Use the Format Function with SaveAs to Add Today's Date in MMDDYY Format
Use this method when you need the workbook filename to include the current date as text in MMDDYY format, such as in a daily PM record.
The Format function converts today's date to a text string so it can be safely joined with the folder path and filename. Using the macro-enabled file format (.xlsm) preserves the VBA project after saving.
In WPS Spreadsheets, open the workbook that contains the macro, click the Developer tab, and then click Visual Basic. If the Developer tab is not visible, enable it from the ribbon settings or press Alt+F11.
In the Visual Basic Editor, click Insert > Module to create a new code module.
Paste the following code into the module, then change the folder path and fName value to match your file location and job number: Dim CurrDate As String CurrDate = Format(Date, "MMDDYY") ActiveWorkbook.SaveAs Filename:="C:\MOLD PM RECORDS\" & fName & "\PM Record - " & fName & " - " & CurrDate & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Click anywhere inside the procedure, then press F5 or click Run > Run Sub/UserForm to save the active workbook with today's date in MMDDYY format.
Save an Excel File with Today's Date Using a VBA Macro in WPS Spreadsheets
WPS Spreadsheets can run VBA macros that use the same Format and SaveAs commands as Excel, so you can automate daily file naming without switching to Microsoft Office.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open your Excel or WPS workbook, and confirm it is saved as a macro-enabled .xlsm file.
- 2. Open the Visual Basic Editor: Click the Developer tab and then click Visual Basic, or press Alt+F11 to open the editor.
- 3. Insert and run the date-stamp macro: Insert a module, paste the Format(Date, "MMDDYY") SaveAs code, and run it with F5 to save the file with today's date.
Frequently Asked Questions
Does this macro work in WPS Spreadsheets if the file is .xlsm?
Yes. WPS Spreadsheets supports VBA macros and the macro-enabled .xlsm format on Windows. Open the workbook, access the Visual Basic Editor from the Developer tab or with Alt+F11, and run the macro after saving the file as .xlsm.
How do I change the date format from MMDDYY to another format?
Edit the Format function argument in the VBA editor. For example, use Format(Date, "DDMMYY") for day-month-year or Format(Date, "YYYYMMDD") for an eight-digit sortable date.
Why should the date be formatted as text before adding it to the file path?
The Format function returns a string, which avoids type mismatch or locale-dependent automatic conversion and ensures the filename always uses the MMDDYY pattern before the .xlsm extension.
Where do I set the folder path and job number?
Change the fName variable or the Filename:= path in the macro code to your target folder and job number. For example, update "C:\MOLD PM RECORDS\" to your actual folder path and set fName to the job number.
