How to Save Workbook Using VBA

So, you’re working with VBA and need to save your Excel workbook? No worries, you’ve come to the right place!

VBA has the SAVE method you can use to save Excel workbooks easily.

In this tutorial, I will show you several examples where you can use VBA to save an Excel workbook.

Table of Contents

Scenario #1: Save a Workbook in its Current Location With Its Current Name

We use the ‘Save’ method to save a workbook in its current location with its current name.

Here’s the code to save the active or current workbook:

Sub SaveCurrentWorkbook()

    ' Save the current workbook
    ThisWorkbook.Save

End Sub

This code saves the workbook that contains the VBA macro code when you run it. It does so by using the ThisWorkbook.Save method, which specifically targets the workbook where the code is located and saves any changes you’ve made.

To save a workbook that is not the current workbook, we must specify the name of the workbook. For instance, the following code saves a workbook called “Sales.”

Sub SaveNonCurrentWorkbook()

    ' Save the workbook named "Sales.xlsx"
    Workbooks("Sales.xlsx").Save

End Sub

The code saves a workbook named “Sales.xlsx” that is currently open in Excel. It uses the Workbooks collection to specify which workbook to save by providing the name “Sales.xlsx”. The .Save method is then applied to save that specific workbook.

Note: The target workbook must be open for this code to work. Otherwise, VBA will throw runtime error 9: subscript out of range.

If the current workbook has never been saved, VBA will prompt you to save it as a macro-free (*.xlsx) or macro-enabled workbook (*.xlsm).

If you click “Yes,” the workbook will be saved as a macro-free workbook (*.xlsx). If you click “No,” VBA will throw a runtime error 1004.

If you want to retain the code in the workbook, you must save the workbook as a macro-enabled workbook (*.xlsm) using the Excel user interface options.

Also read: Run Macro In Another Workbook Using VBA

Scenario #2: Save All Open Workbooks

To save all open workbooks, we use the ‘Save’ method in a “For Each Next” loop to loop through all open workbooks and save them individually.

The following code saves all open workbooks:

Sub SaveOpenWorkbooks()

    ' Declare a variable of Workbook type
    Dim wb As Workbook
    
    ' Loop through each open workbook
    For Each wb In Workbooks
        ' Save the current workbook in the loop
        wb.Save
    Next wb

End Sub

This code automatically saves all open workbooks in Excel. It first declares a Workbook object variable, wb.

Then, it uses a For Each loop to iterate through each workbook in the Workbooks collection.

Inside the loop, the .Save method is called on the wb object, saving each workbook one at a time. This is useful if you have multiple workbooks open and you want to ensure that all of them are saved with their latest changes.

Also read: VBA Check If Workbook Is Open

Scenario #3: Save a Workbook Under a Different Name, Location, and Other Parameters

If we want to save a workbook under a different name or location, we use the ‘SaveAs’ method, as it allows us to specify the filename and other options.

Understanding the syntax and parameters of the ‘SaveAs’ method is crucial for practical application.

Explanation of the ‘SaveAs’ Method

The syntax of the ‘SaveAs’ method is as follows:

Workbook.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode As xlSaveAsAccessMode = xlNoChange, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local, Workidentity)

Let’s break down the parameters:

Filename: This parameter is required and is a string that specifies the name of the file to be saved. You may include a full path. Otherwise, Excel VBA saves the file in the current folder.
FileFormat: This optional parameter specifies the format you want to save the workbook. Different file formats are available, such as xlOpenXMLWorkbook (value 51) and xlOpenXMLWorkbookMacroEnabled (value 52). If you are working on an existing file, Excel will automatically use the last file format you used for saving. However, if you are working on a new file, Excel will use the default format of your version.
Password: An optional parameter indicating the case-sensitive protection password (max. 15 characters).
WriteResPassword: An optional parameter and a string indicating the write-reservation password for this workbook. If a file is saved with the password, but the password isn’t supplied when opening, the file is opened as read-only.
ReadOnlyRecommended: An optional Boolean parameter that, when set to ‘True,’ displays a message when the workbook is opened recommending that the workbook be opened in read-only mode to prevent accidental changes.
CreateBackup: An optional Boolean parameter that, when set to ‘True,’ creates a file backup.
AccessMode: An optional parameter that specifies the access mode for the file and can be set to ‘xlReadWrite’ or ‘xlReadOnly.’
ConflictResolution: An optional value determining how the “SaveAs” method resolves a conflict while saving the workbook:
- The conflict-resolution dialog box will be displayed if set to “xlUserResolution” or omitted.
- If set to “xlLocalSessionChanges,” the local user’s changes will be automatically accepted.
- If set to “xlOtherSessionChanges,” the changes made by other sessions will override the local user’s changes.

AddToMru: An optional Boolean parameter that adds the file to the list of most recently used (MRU) files when set to’ True.’ The default value is ‘False.’
TextCodepage and TextVisualLayout: Optional parameters for saving as a text file. Usually, they tend to be disregarded.
Local: An optional parameter specifying whether to use the local settings to save the file.

Here are three examples of how to use the ‘SaveAs’ method:

Example #1: Save a Workbook With a Different Name in the Default Location in Current Excel Format

The following code will save the current workbook with a different name in the default location (usually the “Documents” folder) in the current Excel format:

Sub Example_1()

    ' Save the current workbook with a new name "Sales Report"
    ThisWorkbook.SaveAs "Sales Report"

End Sub

The code saves the current workbook under a new name, “Sales Report”. It uses the SaveAs method on the ThisWorkbook object, specifying the new name as an argument.

Note that the workbook will be saved in the default Excel format in the directory where the original workbook resides, unless you specify a different path or format. Keep in mind that if a file with the same name already exists in the directory, this operation will overwrite that file without asking for confirmation.

Note: If the current workbook has never been saved, VBA will prompt you to save it as a macro-free (*.xlsx) or macro-enabled workbook (*.xlsm).

Choose whether to save macro free workbook or macro enabled file

If you choose “Yes,” the workbook will be saved without macros in the .xlsx format. If you choose “No,” VBA will generate a runtime error 1004.

If you want to keep the code in the workbook, save it as a macro-enabled workbook (*.xlsm) using Excel’s user interface options.

Also read: Delete Files in a Folder Using VBA in Excel

Example #2: Save a Workbook With a Different Name in a Specified Folder

The following code will save the current workbook with a different name in a specified location in the current Excel format:

Sub Example_2()

    ' Save the current workbook in a specific directory with the name "Sales Report"
    ThisWorkbook.SaveAs "C:\Excel Tutorials\Sales Report"

End Sub

This code saves the current workbook with a new name, “Sales Report,” in a specific directory (“C:\Excel Tutorials”). It utilizes the SaveAs method on the ThisWorkbook object and provides the full file path as an argument.

If a file with the same name already exists in that directory, it will be overwritten without any confirmation prompt. Make sure the specified directory exists, otherwise, you will get an error.

Example #3: Save a Workbook With a Different Name in Another Format in a Specified Folder

The following code will save the current macro-enabled workbook (*.xlsm) with a different name in a specified location in the default Excel format (*.xlsx):

Sub Example_3()

    ' Save the current workbook in a specific directory with the name "2023 Sales Report" and in .xlsx format
    ThisWorkbook.SaveAs "C:\Excel Tutorials\2023 Sales Report", FileFormat:=51

End Sub

This code saves the current workbook with the name “2023 Sales Report” in a specified directory (“C:\Excel Tutorials”) and sets the file format to Excel 2007-2019 workbook (.xlsx).

The SaveAs method of ThisWorkbook is used, and the FileFormat parameter is set to 51, which corresponds to the .xlsx format.

Providing a FileFormat allows you to ensure that the workbook is saved in the desired format. Be cautious, as an existing file with the same name will be overwritten without any warning. Also, ensure the directory exists to avoid errors.

Note: If the current workbook has never been saved, VBA will prompt you to save it as a macro-free (*.xlsx) or macro-enabled workbook (*.xlsm). Click “Yes” to continue saving as a macro-free workbook.

Example #4: Save a Workbook as a CSV File

The following code will save the current workbook with the default CSV settings with a specified name in a specified path:

Sub Example_4()

    ' Declare a String variable to hold the file name
    Dim MyFileName As String
    
    ' Set the file name and directory
    MyFileName = "C:\Excel Tutorials\Population.csv"
    
    ' Save the current workbook as a CSV file in the specified directory
    ThisWorkbook.SaveAs Filename:=MyFileName, FileFormat:=xlCSV

End Sub

The code saves the current workbook as a CSV (Comma Separated Values) file. It first declares a string variable, MyFileName, to store the full file path and name.

Then it uses ThisWorkbook.SaveAs to save the workbook, specifying both the Filename and FileFormat parameters.

The FileFormat is set to xlCSV, which is Excel’s internal code for the CSV file format. This is useful if you need to save your Excel data in a format that can be easily imported into other software.

Note that when saved as a CSV, only the active sheet’s data will be saved. Also, ensure the specified directory exists to prevent any errors.

Also read: SAVE AS in Excel (Shortcut)

Scenario #4: Save a Duplicate of the Current Workbook

We use the ‘SaveCopyAs’ method to duplicate the current workbook and save it with a different name or location.

Note: The ‘Save’ method saves changes to the current workbook, while the ‘SaveAs’ method saves the current workbook under a new name or format. However, the ‘SaveCopyAs’ method creates a duplicate copy of the workbook and keeps the original workbook open and unchanged.

The syntax for the ‘SaveCopyAs’ method is as follows:

Workbook.SaveCopyAs(Filename)

Here, “Workbook” is the workbook you want to save a copy of, and “Filename” is the path and name for the copy of the workbook that you wish to create.

Example of using the ‘SaveCopyAs’ method in Excel VBA:

Sub WorkbookSaveAsCopy()

    ' Save a copy of the active workbook in a specified directory with the name "2024 Sales Report.xlsx"
    ActiveWorkbook.SaveCopyAs "C:\Excel Tutorials\2024 Sales Report.xlsx"

End Sub

This code saves a copy of the currently active workbook as “2024 Sales Report.xlsx” in the “Excel Tutorials” folder on the C drive.

Note: The ‘SaveCopyAs’ method is helpful when creating backup copies or duplicate workbooks for different purposes without modifying the original.

Also read: VBA to Copy Range to Email Body

Scenario #5: Save the Current Sheet as a Separate Workbook

Sometimes, you may want to save a specific worksheet from the current workbook as a separate file.

The example code below saves the current worksheet in a new workbook using the default settings:

Sub SaveCurrentWorksheetAsNewWorkbook()

    ' Declare variables for the new workbook and the current worksheet
    Dim NewWorkbook As Workbook
    Dim CurrentSheet As Worksheet
    
    ' Set the current worksheet to the active sheet of the workbook containing this code
    Set CurrentSheet = ThisWorkbook.ActiveSheet
    
    ' Create a new workbook
    Set NewWorkbook = Workbooks.Add
    
    ' Copy the current worksheet to the new workbook
    CurrentSheet.Copy Before:=NewWorkbook.Sheets(1)
    
    ' Save the new workbook in a specified directory with the name "2023 Sales.xlsx"
    NewWorkbook.SaveAs "C:\Excel Tutorials\2023 Sales.xlsx"

End Sub

This code saves the currently active worksheet from the workbook containing the code as a new workbook.

It first declares variables for the new workbook and the active worksheet. It then sets the CurrentSheet variable to the active worksheet of the workbook containing the code.

A new workbook is created and stored in the NewWorkbook variable. The Copy method is then used to copy CurrentSheet to the new workbook, placing it before the first worksheet.

Finally, the new workbook is saved using the SaveAs method, specifying the location and name (“2023 Sales.xlsx”) for the new workbook.

This is useful for isolating a worksheet and saving it separately without affecting the original workbook. Make sure the directory exists to prevent any errors.

Also read: VBA Protect Excel Workbook

Scenario #6: Save Workbook With Timestamp

Sometimes, you may want to save a workbook with a timestamp. This approach creates unique filenames with the current date and time to avoid overwriting previous workbook versions.

To save a workbook with a timestamp, you can modify the filename to include the current date and time as part of the filename.

The following code saves the current workbook with a timestamp:

Sub SaveWorkbookWithTimestamp()

    ' Declare a String variable to hold the workbook name
    Dim WorkbookName As String
    
    ' Generate the workbook name with a timestamp
    WorkbookName = "Sales Report_" & Format(Now, "yyyy-mm-dd_hh-mm-ss")
    
    ' Save the current workbook with the generated name
    ThisWorkbook.SaveAs WorkbookName

End Sub

This code saves the current workbook with a name that includes a timestamp.

It first declares a variable called WorkbookName to hold the name of the workbook.

Then it generates this name by appending a timestamp to “Sales Report_” using the Format and Now functions.

The timestamp format is “yyyy-mm-dd_hh-mm-ss”.

Finally, it saves the workbook with this dynamically generated name using ThisWorkbook.SaveAs method.

This is especially useful if you need to save multiple versions of a workbook and want to avoid overwriting previous versions.

Also read: VBA to Print Range of Cells to PDF

Scenario #7: Save a Workbook in XML Data Format

We use the ‘SaveAsXMLData’ method to save a workbook in XML Data format. This method is typically used when you want to export data from an Excel workbook to an XML file while preserving the structure and formatting of the data.

This process is beneficial when you exchange data with other applications or systems requiring XML format.

About XML Format

XML(eXtensible Markup Language) is a plain text-based file format used to store and transport structured data in a human and machine-readable way.

XML is not meant to display data but to store and exchange data, making it a widely used format for data representation, configuration files, and data interchange between different systems and platforms.

Here’s the syntax for the ‘SaveAsXMLData’ method:

expression.SaveAsXMLData(Filename As String, Map As xlMap)

Note: expression is a variable representing a workbook object.

Here’s the explanation of the parameters:

Filename: This is a required parameter and is a string that indicates the name of the file to be saved. You can save the file with a full path; if no path is specified, it will be stored in the current folder.
Map: This is a required parameter. It is the schema map to apply to the data.

Note: This method results in a run-time error if Excel cannot export data with the specified schema map.

Here is an example of how to use the ‘SaveAsXMLData’ in VBA:

Sub SaveWorkbookAsXML()

    ' Ignore errors and proceed to the next line
    On Error Resume Next
    
    ' Declare a Workbook variable to hold the active workbook
    Dim wb As Workbook
    
    ' Set the active workbook to the Workbook variable
    Set wb = ActiveWorkbook
    
    ' Declare an XmlMap variable to hold the XML Map
    Dim xmlMap As XmlMap
    
    ' Assign the XmlMap named "MyXmlMap" to the XmlMap variable
    Set xmlMap = wb.XmlMaps("MyXmlMap")
    
    ' Check if the specified XmlMap is available in the workbook
    If xmlMap Is Nothing Then
        ' Display an error message if XmlMap is not found
        MsgBox "XML Map 'MyXmlMap' not found in the active workbook."
    Else
        ' Save the workbook as an XML file using the specified XmlMap
        wb.SaveAsXMLData "C:\Excel Tutorials\ExportedFile.xml", xmlMap
    End If
    
    ' Reset error handling to default behavior
    On Error GoTo 0

End Sub

This code exports data mapped to the ‘MyXmlMap’ schema map to an XML file named ‘ExportedFile.xml’ in the ‘C:\Excel Tutorials’ folder if the schema map exists in the active workbook.

Note: When working with XML data in Excel VBA, it’s essential to have a good understanding of XML concepts and structures. This understanding is crucial if you need to create custom mappings or work with complex XML structures.

The code is designed to save an Excel workbook as an XML file using a specified XML Map (“MyXmlMap“).

It first sets up an error handler using On Error Resume Next, which allows the code to continue running even if an error occurs.

The workbook that’s currently active is then set to the wb variable. An XmlMap variable is defined and set to the XML Map named “MyXmlMap” in the workbook.

If the XML Map is not found, a message box will pop-up to inform you. Otherwise, the workbook will be saved as an XML file in the specified directory.

Finally, the error handler is reset using On Error GoTo 0.

This script is useful when you want to export your workbook data to XML format, provided an XML Map is available in your workbook. Make sure the directory exists to avoid errors.

Also read: How to Open XML Files in Excel?

This tutorial covered seven different scenarios for saving workbooks using VBA. We hope you found the tutorial helpful.

Other Excel articles you may also like: