Fix Excel VBA SaveAs Error 1004 Using Full File Paths
Question details
The user encounters a runtime Error 1004 during a VBA SaveAs operation while converting XLSM workbooks to XLSX format. The macro incorrectly references an unrelated System32 directory path.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Executing a VBA macro loop to batch convert and save multiple XLSM files as standard XLSX workbooks.
- Observed behavior
- The SaveAs method fails with runtime Error 1004 and attempts to save to the System32 folder instead of the intended target directory. Hardcoding the explicit path temporarily bypasses the error.
Before modifying your VBA script, ensure you have read and write permissions for the target folder and close any open instances of the files you intend to process.
Use Explicit Full File Paths with ThisWorkbook.Path
Resolves path resolution failures by dynamically building explicit source and destination file paths instead of relying on the active or current directory.
When a VBA macro executes without explicitly defined directory paths, the application may default to the system's current working directory, which can sometimes incorrectly resolve to the Windows System32 folder. Dynamically building the full path ensures the SaveAs method accurately targets the correct folder.
In your VBA editor (ALT + F11), declare string variables for both the source directory and the destination filename.
Assign the directory of the current macro workbook to your variable using 'SourcePath = ThisWorkbook.Path & "\"' to guarantee correct folder targeting.
Concatenate the destination folder path with the desired filename and the new '.xlsx' extension to form a complete absolute path.
Modify your SaveAs command to use the complete path variable and explicitly state the format type: 'ActiveWorkbook.SaveAs Filename:=DestinationPath, FileFormat:=xlOpenXMLWorkbook'.
Explicitly Open, Save, and Close Target Workbooks
Prevents active workbook confusion and file locking by explicitly defining and managing the workbook objects within the macro loop.
Run Your Spreadsheets Smoothly with WPS Office
If you are consistently dealing with complex runtime errors or macro configuration issues, consider switching to WPS Office. It provides a lightweight, highly compatible environment for your spreadsheets with excellent support for legacy and modern formats.
Frequently Asked Questions
Why does Excel VBA Error 1004 reference the System32 folder?
When a VBA script uses a relative path or fails to specify a complete file path during a SaveAs operation, the application defaults to the current working directory. In certain environment setups, automated executions, or background processes, this working directory defaults to Windows\System32, causing the operation to fail due to lack of write permissions.
How do I correctly specify the XLSX format in a VBA SaveAs macro?
To properly save a workbook as an XLSX file, you must include the parameter 'FileFormat:=xlOpenXMLWorkbook' in your SaveAs method. Simply changing the file extension in the filename string is not sufficient and will result in a corrupted or unreadable file.
How can I prevent my VBA loop from processing the newly saved XLSX files?
You can prevent endless processing loops by configuring your 'Dir()' function or conditional logic to strictly filter for files ending specifically in '.xlsm'. Alternatively, you can save the newly generated '.xlsx' files into a completely separate destination folder outside of the loop's scanning directory.
