How to Fix VBA SaveAs Error When Saving an XLSM Workbook
Question details
Users encounter an error when using the VBA SaveAs method to save an XLSM workbook with a dynamically generated date and time in the filename.
- Product
- Spreadsheets
- Device & OS
- Windows
- Scenario
- Automating the saving process of an XLSM workbook using VBA scripts that include timestamps in the file path.
- Observed behavior
- The VBA SaveAs method fails and stops the macro execution because the generated file name contains characters that are invalid in the operating system's file paths.
Check your current VBA script to identify how the filename is being generated, particularly looking for any Date, Time, or Now formatting functions.
Replace Invalid Characters in the VBA Date and Time Format
Adjust the VBA script to use valid characters (like hyphens instead of slashes or colons) when formatting the date and time for file names.
Windows operating systems restrict certain characters from being used in file names, including forward slashes (/), backslashes (\), colons (:), asterisks (*), question marks (?), quotation marks ("), less than (<), greater than (>), and pipes (|).
Using the default time format with slashes or colons in your VBA SaveAs code will instantly cause the save operation to fail. Explicitly formatting the timestamp with valid separators resolves this issue.
Open your XLSM workbook and press the ALT + F11 keys on your keyboard to launch the Visual Basic for Applications (VBA) Editor.
In the Project Explorer panel on the left, double-click the specific Module or Worksheet containing your SaveAs macro to view the code.
Find the line of code formatting the date or time, such as Format(Time, "h/mm/ss"). Change the invalid separators to hyphens or underscores, modifying it to Format(Time, "hh-mm-ss").
Click the Run button (green triangle) in the top toolbar or press F5 to execute the macro and verify that the workbook saves successfully to your specified folder.
Use WPS Spreadsheet for Robust VBA and Macro Support
WPS Office provides excellent compatibility with Microsoft Excel macros and VBA scripts. You can easily write, edit, and debug your VBA code to automate repetitive tasks like saving and formatting workbooks.
- 1. Install WPS Office: Download and install WPS Office on your Windows PC from the official website.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the macros.
- 3. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon menu to access macro features.
- 4. Run and Edit VBA: Click on 'Visual Basic' to open the VBA editor, where you can safely edit your SaveAs scripts and run your automation workflows.
Frequently Asked Questions
What characters are strictly forbidden in Windows filenames?
Windows prevents the use of the following nine characters in folder and file names: < > : " / \ | ? *. If your VBA script dynamically generates a filename containing any of these characters, the SaveAs command will fail.
Can I use colons to format the time in my saved Excel file name?
No, colons (:) are invalid characters in Windows file paths. Instead of hh:mm:ss, you should use hyphens or underscores like hh-mm-ss in your VBA formatting string.
Why does the VBA SaveAs macro work on some computers but fail on others?
This usually happens due to different regional settings in Windows. One computer's default short date format might use hyphens (which are valid), while another uses slashes (which are invalid). Explicitly forcing the format in your VBA code ensures it works consistently across all PCs.
How do I correctly save an Excel file with the current date using VBA?
You can use the Date function combined with the Format function in your script. For example: ActiveWorkbook.SaveAs Filename:="Report_" & Format(Date, "yyyy-mm-dd") & ".xlsm". This generates a safe, valid file name.
