Excel VBA File Save As: Complete Guide

VBA & macros · Updated November 22, 2026

The direct answer: in Excel VBA, use ActiveWorkbook.SaveAs with a file path and format number, e.g., ActiveWorkbook.SaveAs "C:\Folder\File.xlsx", 51. This method saves the active workbook under a new name, location, or format, and it’s the core command for any “Save As” automation in Excel.

Why Save As Matters in VBA

VBA’s SaveAs method is essential when you want to create backups, export data to different formats (like CSV or PDF), or generate timestamped reports. Unlike the regular Save method, which overwrites the current file, SaveAs lets you specify a new filename, path, and file format. This is particularly useful in workflows where you need to produce multiple versions of a workbook without losing the original. For example, a monthly report macro might save a copy as Report_Jan2025.xlsx while keeping the template intact.

Basic Syntax and Parameters

The full syntax of the SaveAs method is:

ActiveWorkbook.SaveAs Filename:="path", FileFormat:=51, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Key parameters you’ll use most often:

  • Filename – full path including the file name and extension (e.g., "C:\Reports\Sales.xlsx"). If you omit the path, it saves to the current folder.
  • FileFormat – a number that specifies the format. The most common: 51 (xlsx), 52 (xlsm), 50 (xlsb), 56 (xls), 23 (CSV), 24 (TXT), 43 (xlsm with macros? Actually 43 is xlsb? Wait, check: 43 is xlsb? No, 50 is xlsb. Let’s be precise: 51 = xlsx, 52 = xlsm, 50 = xlsb, 56 = xls (Excel 97-2003), 23 = CSV (comma separated), 24 = CSV (Macintosh), 25 = CSV (MS-DOS), 43 = xlsm? No, 43 is not standard. Actually, 52 is xlsm. For PDF, use 0? No, PDF is not a native SaveAs format; you need ExportAsFixedFormat. So stick to the list above.
  • Password – optional string to set a password for opening the file.
  • WriteResPassword – password to modify the file.
  • CreateBackup – Boolean, True to create a backup file.

Example: Save as a macro-enabled workbook:

ActiveWorkbook.SaveAs Filename:="C:\Macros\MyMacro.xlsm", FileFormat:=52

How to Choose the Right FileFormat Number

The FileFormat number is a constant from the XlFileFormat enumeration. Here are the ones you’ll use 95% of the time:

  • 51 – Excel Workbook (.xlsx), no macros
  • 52 – Excel Macro-Enabled Workbook (.xlsm)
  • 50 – Excel Binary Workbook (.xlsb), faster for large files
  • 56 – Excel 97-2003 Workbook (.xls), for compatibility with old versions
  • 23 – CSV UTF-8? Actually, 23 is CSV (comma delimited) for Windows, but it saves only the active sheet. For UTF-8 CSV, you need to use SaveAs with FileFormat:=62 (Unicode text) or use a different method. Stick to 23 for standard CSV.
  • -4158 – PDF? No, that’s not right. PDF is not a SaveAs format; use ExportAsFixedFormat instead.

To see a full list, in the VBA editor press F2 to open the Object Browser, search for XlFileFormat, and browse the constants. But for most tasks, the above list covers you.

Step-by-Step: Automating Save As with a Macro

Here’s a practical example: saving the active sheet as a CSV file with a dynamic name based on the current date.

  1. Press Alt+F11 to open the VBA editor.
  2. Insert a new module (Insert > Module).
  3. Paste the following code:
Sub SaveAsCSVWithDate()
Dim FilePath As String
Dim FileName As String
' Save a copy of the workbook first (macros cannot be undone with Ctrl+Z)
ThisWorkbook.SaveCopyAs ThisWorkbook.FullName
' Build a filename with today's date
FileName = "Data_" & Format(Date, "yyyymmdd") & ".csv"
FilePath = "C:\Exports\" & FileName
' Save the active sheet as CSV (only the active sheet is saved)
ActiveSheet.SaveAs Filename:=FilePath, FileFormat:=23
MsgBox "Saved to " & FilePath
End Sub

Run the macro with F5. It will save the active sheet as a CSV file in the specified folder. Note: CSV only saves the active sheet, not the entire workbook.

Common Problems and Solutions

Several issues trip up beginners:

  • Error 1004 (Method ‘SaveAs’ of object ‘_Workbook’ failed) – Usually caused by an invalid path or filename (e.g., containing illegal characters like : or *), or the folder doesn’t exist. Ensure the folder exists before saving; VBA won’t create it automatically.
  • File already exists – By default, SaveAs overwrites without warning. If you want to avoid overwriting, check with Dir() first:
If Dir(FilePath) <> "" Then
MsgBox "File already exists."
Exit Sub
End If
  • Saving as CSV loses formatting – CSV is plain text; only values are saved. If you need formatting, save as xlsx.
  • Macros are lost when saving as xlsx – If your workbook contains VBA code, saving as .xlsx will strip the macros. Use .xlsm (FileFormat:=52) instead.
  • Relative paths – If you use a relative path like "Reports\File.xlsx", it saves relative to the current working directory, which might not be the workbook’s folder. Use ThisWorkbook.Path to get the workbook’s folder:
Dim Path As String
Path = ThisWorkbook.Path & "\Reports\" & FileName

Save As vs. SaveCopyAs vs. ExportAsFixedFormat

Three methods often get confused:

  • SaveAs – Changes the active workbook’s name and format, and the original file is no longer open. Use when you want to continue working with the new file.
  • SaveCopyAs – Saves a copy of the workbook to a new location without changing the current file. The original remains open and unchanged. Good for backups.
  • ExportAsFixedFormat – Exports to PDF or XPS. This is not a SaveAs; it creates a separate file without affecting the workbook. Example:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Reports\Report.pdf"

Choose based on your goal: if you want to keep working in the new file, use SaveAs; if you just want a copy, use SaveCopyAs; if you need a PDF, use ExportAsFixedFormat.

Safety and Best Practices

When automating saves, follow these rules to avoid data loss:

  • Always save a backup copy before running a macro that overwrites files. Use ThisWorkbook.SaveCopyAs as shown above.
  • Test on a dummy file first, especially if the macro deletes or overwrites existing files.
  • Use error handling to catch issues gracefully:
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=51
If Err.Number <> 0 Then
MsgBox "Save failed: " & Err.Description
Err.Clear
End If
On Error GoTo 0
  • Be aware of file format limitations – For example, CSV only saves the active sheet, and xlsx cannot store macros. Choose the format that matches your needs.
  • Mac vs. Windows – The path separator is different. On Mac, use : or the Mac path format (e.g., Macintosh HD:Users:Name:Documents:File.xlsx). VBA on Mac uses the same SaveAs method, but paths differ. For cross-platform code, use Application.PathSeparator to get the correct separator.

Frequently Asked Questions

How do I save a workbook as a CSV file with UTF-8 encoding?

The standard SaveAs with FileFormat:=23 saves as ANSI, which breaks special characters. To save as UTF-8, you need to use FileSystemObject or write the data line by line. A common workaround is to save as a .txt file with FileFormat:=62 (Unicode text) and then change the extension, but that’s not perfect. For full control, use ADODB.Stream to write UTF-8. However, for most simple cases, FileFormat:=23 is sufficient if your data is ASCII.

Why does my macro fail when saving to a network drive?

Network drives can have permission issues or latency. Ensure the folder path is correct and you have write access. Also, avoid using mapped drive letters (like Z:\) because they may not exist on other machines. Use the UNC path (e.g., \\Server\Share\Folder\) for reliability. If it still fails, add a small delay or retry loop.

Can I save a specific sheet as a separate workbook?

Yes, copy the sheet to a new workbook and save that. For example:

Dim NewWB As Workbook
Set NewWB = Workbooks.Add
ThisWorkbook.Sheets("Report").Copy Before:=NewWB.Sheets(1)
NewWB.SaveAs Filename:="C:\Reports\Report.xlsx", FileFormat:=51
NewWB.Close SaveChanges:=False

This creates a new workbook with just the copied sheet.

What is the difference between FileFormat 51 and 52?

51 is .xlsx, which cannot contain macros. If your workbook has VBA code, you must use 52 (.xlsm) to keep the macros. If you save a macro-containing workbook as .xlsx, Excel will either strip the macros or prompt you to save as .xlsm. Always use 52 for macro-enabled files.

How do I save a file with a password using VBA?

Use the Password parameter in SaveAs:

ActiveWorkbook.SaveAs Filename:="C:\Secure.xlsx", FileFormat:=51, Password:="mypassword"

This sets a password to open the file. To set a password for modifying, use WriteResPassword. Remember that passwords are case-sensitive and cannot be recovered if forgotten.

Does SaveAs work in Excel for the web?

No, VBA is not supported in Excel for the web. You can only use VBA in the desktop versions of Excel (Windows and Mac). For web automation, you’d need Office Scripts or other methods. If you’re using a macro that includes SaveAs, it will only run in the desktop client.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.