Table of Contents
Using Visual Basic for Applications (VBA), you can save a workbook by calling the built-in Workbook.Save method on the applicable Workbook object. The method takes in a parameter for the file path and name of the output file, which can be specified in the code or determined at run-time. Alternatively, the Workbook.SaveAs method can be used to open a dialog box and allow the user to select the desired file path and name. Once the file path and name have been specified, the workbook will be saved to the designated location.
You can use the following syntax in VBA to save and close a workbook:
Sub SaveClose()
ActiveWorkbook.Close _
SaveChanges:=True, _
Filename:="C:UsersbobDesktopMyExcelFile.xlsx"
End Sub
This particular macro will save the most recent changes to the currently active workbook and then close it.
The Filename statement specifies the location to save the workbook.
Note that if you don’t want to save the changes before closing the workbook, you could use the statement SaveChanges:=False instead.
The following example shows how to use this syntax in practice.
Example: Save and Close Workbook Using VBA
Suppose we have the following Excel workbook open and we’re viewing it:
Suppose we would like to save the most recent changes we’ve made to this workbook and then save it on our Desktop.
We can create the following macro to do so:
Sub SaveClose()
ActiveWorkbook.Close _
SaveChanges:=True, _
Filename:="C:UsersbobDesktopMyExcelFile.xlsx"
End Sub
Once we run this macro, the most recent changes are saved and the workbook is automatically closed.
I can then navigate to the Desktop on my computer and find the Excel workbook saved:
The workbook has been saved under the name MyExcelFile.xlsx, just as we specified using the Filename statement.
Note: You can find the complete documentation for the Workbook.Close method in VBA .