Runtime Error 1004: “SaveAs method of Workbook class failed.”

VBA raises Runtime Error 1004: “SaveAs method of Workbook class failed” when the Workbook.SaveAs method cannot write the file to the specified destination. This typically occurs because the chosen FileFormat conflicts with the file extension, the macro tries to save VBA code into an extension that does not support macros (.xlsx), or the file system blocks the write operation.

Quick Answer

Ensure the FileFormat enumeration matches the file extension (for example, use FileFormat:=xlOpenXMLWorkbookMacroEnabled for .xlsm files), verify that the destination directory exists and is writable, confirm that no existing file with that name is currently open or locked, and set Application.DisplayAlerts = False to suppress overwrite confirmation popups.

---

Root Causes of SaveAs Method Failures

The SaveAs method must balance file system rules, user permissions, and Office Open XML format constraints. When any of these parameters mismatch, Excel halts:

1. File Extension and Format Discrepancies: Saving a workbook with .xlsx while specifying xlOpenXMLWorkbookMacroEnabled (or vice versa).
2. Stripping VBA from Macro Workbooks: Attempting to save a workbook containing VBA macros as a standard .xlsx without silencing the native security warning or converting the format.
3. Overwrite Prompts with DisplayAlerts Active: An existing file with the same name is already on disk. Excel triggers a “Do you want to replace it?” prompt. If VBA cannot get a user response, it fails.
4. Invalid Characters or Directory Paths: The target filename contains illegal characters (\, /, :, , ?, ", <, >, |) or points to a directory that does not exist.

---

How to Fix SaveAs Operations

1. Align the FileFormat Parameter with the Extension

Never rely on the file extension alone to dictate the file’s internal structure. Always provide the explicit XlFileFormat enumeration:

Sub SaveWorkbookSafely()
    Dim wb As Workbook
    Dim savePath As String

    Set wb = ThisWorkbook
    savePath = "C:\Reports\MonthlyExport.xlsm"

    ' Suppress overwrite prompts
    Application.DisplayAlerts = False

    ' Explicitly align .xlsm with xlOpenXMLWorkbookMacroEnabled (52)
    wb.SaveAs Filename:=savePath, _
              FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
              CreateBackup:=False

    Application.DisplayAlerts = True
End Sub

Common FileFormat Enumerations:

• .xlsx (No Macros): xlOpenXMLWorkbook (Value: 51)
• .xlsm (Macro-Enabled): xlOpenXMLWorkbookMacroEnabled (Value: 52)
• .xlsb (Binary Format): xlExcel12 (Value: 50)
• .csv (Comma Delimited): xlCSV (Value: 6)

2. Saving Macro-Free Copies (.xlsx) from an Active .xlsm

If your macro generates a clean client report without any macro code, you must specify xlOpenXMLWorkbook and manage alert suppression:

Sub ExportCleanReport()
    Dim newWb As Workbook
    Set newWb = Workbooks.Add

    ' Copy summary sheet to new workbook
    ThisWorkbook.Worksheets("Report").Copy Before:=newWb.Sheets(1)

    Application.DisplayAlerts = False
    newWb.SaveAs Filename:="C:\Reports\CleanReport.xlsx", _
                 FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True

    newWb.Close SaveChanges:=False
End Sub

3. Verify Folder Existence Before Saving

If a variable defines the destination folder dynamically (for example, by date or user), verify that the directory exists before executing SaveAs:

Dim folderPath As String
folderPath = "C:\Reports\" & Year(Date) & "\"

If Dir(folderPath, vbDirectory) = "" Then
    MkDir folderPath ' Create folder if missing
End If

For broader file recovery and save diagnostics, see Fixing “Errors were detected while saving ‘X’. Microsoft Excel may be able to save the file…” and “Sharing Violation”: Why Excel won’t let you save over an existing file..