Fix Excel VBA SaveAs Run-Time Error 1004 for XLTM Templates
Question details
The user encounters run-time error 1004 when using an Excel VBA macro to save an XLTM template, with the failure occurring inconsistently across different Excel versions or user profiles.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Executing an ActiveWorkbook.SaveAs VBA macro that dynamically generates a file name from worksheet values inside an opened XLTM template.
- Observed behavior
- The macro triggers run-time error 1004 during the save process, often due to dynamically generated file names containing invalid characters or incorrect workbook references.
Before modifying your VBA code, ensure you check the regional date and time settings on the affected computer, as different system formats can inadvertently introduce illegal characters into your dynamically generated file names.
Inspect and Format the Generated File Name
Use the VBA editor to step through your code and verify that the dynamically generated file name contains a valid path, correct formatting, and no prohibited Windows characters.
Run-time error 1004 during a SaveAs operation most commonly occurs when the FileName string contains invalid characters (such as colons or slashes generated by unformatted date/time values) or points to a non-existent directory.
By inspecting the variable at runtime, you can identify exactly what Excel is attempting to save and correct the string formatting.
Press Alt + F11 to launch the Visual Basic for Applications editor, and locate the macro responsible for the SaveAs operation.
Place your cursor at the beginning of the macro and press F8 repeatedly to execute the code line-by-line until you reach the SaveAs command.
Hover your mouse cursor over the variable or cell reference (e.g., cell R2) defining the file name. Check for blank values or illegal Windows characters like colons, slashes, or asterisks.
Modify your VBA code to force a safe string format. For example, use Format(Now, "yyyymmdd_hhmmss") to ensure date and time values do not produce slashes or colons in the final file path.
Adjust Workbook References for Templates
Update your macro to reference the correct workbook instance, as opening an XLTM template creates a new unsaved workbook that might not be targeted correctly by ActiveWorkbook.
Experience Seamless Spreadsheet Management with WPS Office
If you frequently encounter VBA run-time errors or compatibility issues in Microsoft Excel, consider trying WPS Office. It provides a lightweight, highly compatible environment for your daily spreadsheet tasks, offering seamless support for standard Excel formats and an intuitive user interface.
Frequently Asked Questions
What causes Run-Time Error 1004 in Excel VBA?
Run-time error 1004 is a generic application or object-defined error. During a SaveAs operation, it is most commonly triggered by an invalid file path, illegal characters in the generated file name, insufficient folder permissions, or attempting to save over an open read-only file.
How do I remove invalid characters from a file name in VBA?
You can use the VBA Replace function to substitute illegal characters before saving. For example, Replace(FileName, "/", "-") will swap forward slashes for dashes, ensuring the string can be legally used as a Windows file name.
Why does my Excel macro work on one computer but fail on another?
Differences in user environments can break macros. This includes varying default file save locations (like synced OneDrive folders), different Excel version security settings, or mismatched Windows regional settings that alter how dates and times are formatted in text strings.
What is the difference between ActiveWorkbook and ThisWorkbook in VBA?
ActiveWorkbook refers to the workbook window that is currently in focus or actively being interacted with by the user. ThisWorkbook specifically refers to the exact workbook file where the currently executing VBA code resides, regardless of which window is active.
