VBA Save Workbook (Excel File)

To save an Excel workbook using VBA, you need to use the SAVE method to write a macro. And in that macro, you need to specify the workbook that you want to save and then use the SAVE method. When you run this code, it works like the keyboard shortcut (Control + S).

1. Specify the workbook hat you want to save.
2. Type a dot to get the list of all the properties and methods.
3. Select the “Save” method out of those or type “Save”
4. In the end, run the code to save the workbook.

In this tutorial, we will look at different ways that we can use to save a workbook. So make sure to open the VBA editor from the developer tab to use the code you have in this tutorial.

Save the ActiveWorkbook

If you want to save the active workbook in that case you can use a code like the following code, instead of specifying the workbook by its name.

ActiveWorkbook.Save

When you use the ActiveWorkbook as the workbook, VBA always refers to the workbook which is active despite in which file you are writing the code.

Save the Workbook where you are Writing Code

If you want to save the file where you are writing the code you need to use “ThisWorkbook” instead of the workbook name.

ThisWorkbook.Save

Save All the Open Workbooks

Here we can use a loop to loop through all the workbooks that are open and save them one by one. Look at the below code.

Sub vba_save_workbook()
'variable to use as a workbook
Dim wb As Workbook
'For each to loop through each open workbook and save it
For Each wb In Workbooks
    wb.Save
Next wb
End Sub

The above code uses the FOR EACH loop in each workbook it uses the SAVE method for each file one by one.

Note: If you are trying to save a workbook with the SAVE method that is not saved already, Excel will show a dialog box to ask for your permission to save that file, and then you need to choose if you want to save that file on the default location in the default format.

Now here’s the point: As you are using a macro to save the workbook, that file should be saved in the macro-enabled format and the best way to deal with this situation is to use the SAVE AS method (we’ll see in the next section of this tutorial).

Save As an Excel File

To SAVE a file that is not saved yet, using VBA,  you need to use the SAVE AS method. In this method, you can define the file name and the path where you want to save the file, and apart from that, there are ten more arguments that you can define.

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

In the following code, you don’t have any argument with the “SAVE AS” method.

When you run this code, it asks you a few things, like, which format you want to use to save the file, or do you want to replace the existing file that is already saved with the same name. So it’s better to define the use of some of the arguments.

Save As a File on the Current Location

By default, VBA uses the current location to save the file. When you write code with the SAVE AS method and just specify the name that file straight goes to the current folder. You can see in the following code where you have the which saves the active workbook.

Sub save_as_file()
    ActiveWorkbook.SaveAs Filename:="myNewWorkbook"
End Sub

Save As a File on a Specific Location

The filename argument also allows you to use the location path in case you want to use a different location to save the file.

Sub save_as_file()
    ActiveWorkbook.SaveAs _
        Filename:="C:UsersDellDesktopmyNewBook"
End Sub

In the above code, you have the path in the FileName argument and VBA uses that path to the file.

Note: You can also use this method to check if a workbook exists in a folder or not before you use the SAVE AS method to save it on a particular location and you can learn more about the SAVE AS method from here.