VBA Save File – 20 Easy Examples

This VBA Tutorial covers how to save a file using the Save and Save As commands in VBA.

Save Workbook – VBA

The VBA Save command saves an Excel file similarly to clicking the Save icon or using the Save Shortcut (CTRL + S).

Save a Specified Workbook

To save a workbook, reference the workbook object and use the Save command.

Workbooks("savefile.xlsm").Save

Save the Active Workbook

Note: This is the current active workbook from with in the VBA code, which is different from ThisWorkbook which contains the running code.

ActiveWorkbook.Save

Save the Workbook Where the Code is Stored

ThisWorkbook.save

Save all Open Workbooks

This will loop through all open workbooks, saving each one.

Dim wb as workbook

For Each wb In Application.Workbooks
	wb.Save
Next wb

Save all open workbooks that were not opened ReadOnly

Note: opening a workbook in ReadOnly mode prevents the file from being saved.
To save the file you will need to use Save As and save the file with a different name.

Dim wb as workbook

For Each wb In Application.Workbooks
	If not wb.ReadOnly then
		wb.Save
	End if
Next wb

Save a workbook defined by a variable

This will save a workbook that was assigned to a workbook object variable.

Dim wb as workbook

set wb = workbooks("savefile.xlsm")
wb.save

Save a workbook defined by a string variable

This will save a workbook that’s name was saved to a string variable.

Dim wbstring as string

wbstring = "savefile.xlsm"
workbooks(wbstring).save

Save a workbook defined by the order it was opened.

Note: The first workbook opened would have 1, the second 2, etc.

workbooks(1).save

Save a workbook based on a cell value

This will save a workbook that’s name is found in a cell value.

Dim wbstring as string

wbstring = activeworkbook.sheets("sheet1").range("wb_save").value
workbooks(wbstring).save

Save As – VBA

The VBA Save As command saves an Excel file as a new file, similar to clicking the Save As icon or using the Save As Shortcut (Alt > F > A).
Above, we identified all the ways to specify which workbook to save. You can use those exact same methods to identify workbooks when using Save As.

Save As behaves similarly to Save, except you also need to specify the name of the new file.
In fact, Save As has many potential variables to define:

SaveAs Syntax:

workbook object .SaveAs(FileName, FileFormat, Password, WriteResPassword, _
ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, _
AddToMru,TextCodepage, TextVisualLayout, Local)

A full description of all of the SaveAs arguments is included below. For now we will focus on the most common examples.
Note: These arguments can be entered as string with parenthesis or as defined variables.

Save As Syntax Examples:

Workbook Save As – Same Directory

ActiveWorkbook.SaveAs Filename:= "new"

or

ActiveWorkbook.SaveAs "new"

or

Dim wbstring as string

wbstring = "new"
ActiveWorkbook.SaveAs Filename:= wbstring

Workbook Save As – New Directory

ActiveWorkbook.SaveAs Filename:= "C:\new"

or

Dim wbstring as string

wbstring = "C:\new"
ActiveWorkbook.SaveAs Filename:= wbstring

Workbook Save As – New Directory, Specify File Extension

ActiveWorkbook.SaveAs Filename:= "C:\new.xlsx"

or

Dim wbstring as string

wbstring = "C:\new.xlsx"
ActiveWorkbook.SaveAs Filename:= wbstring

Workbook Save As – New Directory, Specify File Extension – Alt Method

You can also specify the file format in it’s own argument.

.xlsx = 51 '(52 for Mac)
.xlsm = 52 '(53 for Mac)
.xlsb = 50 '(51 for Mac)
.xls = 56 '(57 for Mac)

ActiveWorkbook.SaveAs Filename:= "C:\new", FileFormat:= 51

Workbook Save As – Add Password to Open File

ActiveWorkbook.SaveAs Filename:= "C:\new.xlsx", Password:= "password"

Workbook Save As – Add Password for Write Privileges

If correct password is not supplied then workbook opens as Read-Only

ActiveWorkbook.SaveAs Filename:= "C:\new.xlsx", WriteRes:= "password"

Workbook Save As – Read-Only Recommended

TRUE to display a message box, recommending that the file is opened read-only.

ActiveWorkbook.SaveAs Filename:= "C:\new.xlsx", ReadOnlyRecommended:= TRUE

Other Save As Examples

Create Save As Dialog Box

This Generates the Save As Dialog Box, prompting the user to Save the file.
Keep in mind that this simple code may not be appropriate in all cases.

Application.GetSaveAsFilename

Create Save As Dialog Box with Default File Name Provided

Application.GetSaveAsFilename InitialFilename:="test.xlsx"

Create Save As Dialog Box with Default File Name Provided

Application.GetSaveAsFilename InitialFilename:="test.xlsx"

Create & Save New Workbook

This will create a new workbook and immediately save it.

Dim wb As Workbook

Set wb = Workbooks.Add
Application.DisplayAlerts = False
wb.SaveAs Filename:=”c:\Test1.xlsx”
Application.DisplayAlerts = True

Disable Save Alerts

As you work with saving in VBA, you may come across various Save Warnings or Prompts. To disable warnings, add this line of code:

Application.DisplayAlerts=False

and to re-able alerts:

Application.DisplayAlerts=True