How to Save as in Microsoft Excel VBA

Workbook.SaveAs saves a workbook under a new name or path. Use it when you need to choose a destination or file format; use Workbook.Save to write changes to the workbook’s current file. In Excel, the corresponding manual commands are File > Save and File > Save As, and Ctrl+S saves the current file.

What SaveAs Does and When to Use It

Call SaveAs on the workbook you intend to save. It can create a file at a new location and can also change the file format. Saving under another name leaves the original file in place, while saving again to an existing destination may prompt before replacing it. A workbook must be saved to a writable location, and its filename extension should match the selected format.

The receiver matters: ActiveWorkbook refers to the workbook currently active in Excel, which may not be the workbook containing the macro. If the code is stored in the workbook that should be saved, ThisWorkbook identifies that workbook. For other cases, keep a Workbook object reference and call the method on that object.

SaveAs Syntax and Key Arguments

The method’s general form is workbook.SaveAs Filename:=path, FileFormat:=format. Filename is the destination name, optionally including a full path. FileFormat selects the file type; for example, use xlOpenXMLWorkbookMacroEnabled with an .xlsm filename and xlOpenXMLWorkbook with .xlsx. Choose a format that preserves any workbook features the reader needs.

Other arguments are optional. Password protects the file from opening without the supplied password, and WriteResPassword controls write access. ReadOnlyRecommended:=True asks Excel to recommend read-only mode; CreateBackup:=True requests a backup. Omit these arguments when they are not needed rather than passing empty password strings. AccessMode, ConflictResolution, and AddToMru affect access, conflict handling, and the recent-files list. Local:=True uses Excel’s language settings, while Local:=False uses VBA’s language; False is the default. Most calls need only a filename and, when the extension alone is not sufficient, a format.

Save a Workbook to a Specified Path

Use a full path and a format that agrees with the extension. For a workbook already open under the name Example.xlsm, the call is Workbooks("Example.xlsm").SaveAs Filename:="C:\Exports\Example.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled. This assumes C:\Exports exists and is writable. If the source workbook has unsaved changes, this saves them to the new destination; it does not remove the original file.

To create a new workbook and save it directly, assign the result of Workbooks.Add to a Workbook variable, then call newBook.SaveAs Filename:=... on that variable. Declare the variable as Workbook and provide the same path and format considerations. Excel may show a confirmation when the destination already exists.

Choose a File Name Interactively

Application.GetSaveAsFilename opens a file-name dialog and returns the selected name; it does not save the workbook itself. Its return value is a Variant: a string when the user chooses a name, or Boolean False when the user cancels. Check for cancellation before passing the result to SaveAs.

A typical sequence is Dim newBook As Workbook, Dim fileName As Variant, Set newBook = Workbooks.Add, fileName = Application.GetSaveAsFilename(InitialFileName:="Report.xlsm", FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm"), then If VarType(fileName) = vbBoolean Then Exit Sub. After that check, save with newBook.SaveAs Filename:=CStr(fileName), FileFormat:=xlOpenXMLWorkbookMacroEnabled. The new workbook remains open if the user cancels. Add any required extension or format choice deliberately; selecting a name in the dialog alone does not determine that the workbook’s contents are compatible with the extension.

Save the Intended Workbook

For the workbook currently selected in Excel, use ActiveWorkbook.SaveAs Filename:="C:\Exports\Current.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled. Use this only when the active workbook is the intended target; activating another workbook before the call changes what ActiveWorkbook refers to.

To save each open workbook, iterate over the Workbooks collection and call SaveAs on the loop variable, not on ActiveWorkbook. For example, the essential loop is Dim wb As Workbook, For Each wb In Workbooks, wb.SaveAs Filename:="C:\Exports\" & wb.Name, and Next wb. Ensure the destination folder exists and that each workbook’s filename extension and format agree. This loop saves every workbook in the collection, including a workbook containing the macro; use a narrower selection if that is not intended. Saving multiple workbooks can trigger overwrite or format prompts. These examples show VBA source and do not include observed Excel execution results.