What Is VBA Workbook.close?
VBA workbook.close method is used in VBA to close a workbook in an Excel application. Consider the following example for VBA workbook.close: This code highlighted below closes the workbook Sample1.XLS by saving all the changes made to the workbook.
Workbooks(“Sample1.XLS”).Close SaveChanges:=True
In the following sections, we will look at the syntax and how to use the VBA workbook.close in different examples.
Key Takeaways
- VBA workbook.close method is used in VBA to close a workbook in an Excel application.
- This method has a mandatory expression as a parameter and 3 optional parameters using which you can control the behavior of the method outcomes.
- To use a workbook.close method, you can use VBA scripts where you can specify whether to close a workbook with or without saving changes to your workbook.
- Always remember to specify the SaveChanges parameter to ensure the workbook is either saved or not saved after any changes to the workbook.
Syntax
The syntax for the workbook.close method in VBA is as provided below:
expression.Close (SaveChanges, FileName, RouteWorkbook)
Let’s look at each of the parameters and expressions to understand it better.
- expression represents a Workbook object i.e. Microsoft Excel workbook.
- SaveChanges is an optional parameter that has a variable data type.
- This argument in the close method is ignored when the workbook you referred to has not been changed.
- Similarly, this argument is ignored when the workbook is changed and appears in another open window.
- For any changes to the workbook where the workbook has not appeared in any other open windows, then this argument highlights whether the changes are to be saved or not.
- When this parameter is set to True, any changes to the workbook are saved.
Note: If the workbook does not have file names associated with it, FileName is used. If FileName is missing, the user is prompted to provide a file name.
- The Filename is an optional parameter that has a variable data type.
- All the changes to the workbook are saved to the file name provided in this parameter.RouteWorkbook parameter is an optional argument with variant data type.It can have True or False values and primarily instructs the VBA code to route the workbook as per the values specified in the parameter.
- If this is set to True, the close method will send the workbook to the next recipient.
How to use the VBA workbook.close?
To use the VBA workbook.close method in your application, follow the instructions outlined below to apply in your applications.
Step 1: Open your data source in Excel. In Excel, enable VBA tools by navigating File – Options – Customize Ribbon and checking the Developer option..
Step 2: Open the VBA Editor by pressing ALT + F11 or navigating to the Developer àVisual Basic option.
Step 3: In the VBA Editor, insert a new module. Do it by right-clicking on Module – Insert – Module.
Step 4: Specify the VBA script to apply the copy range on your Excel data source. Here you need to specify the logic by using the workbook.close method.
Step 5: Give the macro a meaningful name and run it by pressing F5 or clicking on the Run icon from the icon toolbar.
The VBA script will then perform the task according to the specified code, and you will be able to see the changes in the sheet.
Now that we have understood the syntax and how to use the workbook.close, let’s look at some of the examples to apply this method.
Examples
In this section, we will review different examples to demonstrate the VBA workbook. We will also discuss close syntax and create functions to help you implement it in real-world scenarios.
Example #1 – Close a Workbook without Saving
In this example, we will demo opening a workbook and then close it without saving any changes made to the workbook.
To close a workbook without saving any changes using VBA, follow the instructions outlined below:
Step 1: Create a new blank Excel workbook Demosheet.xlsx in your local folder, as shown below.
As you can see, the Demosheet workbook has no available content.
Step 2: Navigate to Developer – Visual Basic in the Demosheet Excel workbook.
It will open a VBA editor screen for the Demosheet.xlsx file as shown below.
Step 3: Under VBAProject, right-click on the Module and choose Insert – Module option from the context menu.
Step 4: Click on the Run icon in the toolbar. It will open a Macros screen. Specify a Macro Name and click on the Create button.
Here, we have provided the macro name as Workbook_Open_Close_NoSave.
Step 5: In the VBA editor, specify the logic to close a workbook without saving the changes.
For this demonstration, we have specified the VBA code below and added comments to clarify each step.
Sub Workbook_Open_Close_NoSave()
‘Open the Demosheet file
Workbooks.Open Filename:=”C:\Users\<Local file location>\Demosheet.xlsx”
‘Add text to the sheet
Workbooks(“Demosheet.xlsx”).Sheets(1).Range(“B2:B10”) = “Changes Added to this sheet”
‘Don’t save changes and close
Workbooks(“Demosheet.xlsx”).Close SaveChanges:=False
End Sub
Once you have specified the code changes, click on save changes.
Step 6: Now click on the Run icon in the toolbar. To understand the steps, we have applied the debug pointers at each step.
Click on the Run icon. You will see the execution is at the first step, i.e., Opening the Demosheet file based on the local path you have provided.
Step 7: Now click on Run to move to the next step.
This step will add text to the sheet in cells B2 to B10, as shown below.
Step 8: Click on the Run Macro icon again to move to the next step.
In this step, the code will close the Demosheet.xlsx file without saving any changes we have applied in the previous step by specifying the SaveChanges parameter as False.
After the execution is complete, open the Demosheet.xlsx file on your machine. You will notice that all the changes made are not saved in your Excel workbook.
Example #2 – Close a Workbook after Saving
In this example, we will demonstrate opening a workbook, applying changes to the workbook, and then closing it after saving the changes made to the workbook.
We have created a new Excel workbook DemosheetSave.xlsx, for this purpose.
To close a workbook after saving changes, follow the step-by-step instructions outlined below:
Step 1: Create a new Excel workbook, DemosheetSave.xlsx, in your local folder.
As you can see, there is no content available on the DemosheetSave.xlsx worksheet.
Step 2: Navigate to the Developer – Visual Basic.
It will open a blank VBA editor, as shown below.
Step 3: Open the VBAProject window. Right-click on the Modules under VBAProject and choose Insert à Module.
It will open a new blank VBA editor screen, as shown below. Rename the module to Workbook_Open_Close_Save.
Step 4: Click on the Run icon in the icon toolbar. It will open up a Macros window. Specify the Macro Name and click on Create to create a macro.
Step 5: In the VBA editor, specify the logic to close a workbook without saving the changes.
You can specify the code provided below
Sub Workbook_Open_Close_Save()
‘Open the Demosheet file
Workbooks.Open Filename:=”C:\Users\<local file>\DemosheetSave.xlsx”
‘Add text to the sheet
Workbooks(“DemosheetSave.xlsx”).Sheets(1).Range(“A2:B10”) = “New Content Added”
‘Don’t save changes and close
Workbooks(“DemosheetSave.xlsx”).Close SaveChanges:=True
End Sub
Step 6: Now run the macro to see the behavior of the code you have specified in the VBA editor. To demonstrate the effect, we have applied debug points at each step.
Once you execute the highlighted step above, VBA will open the DemosheetSave.xlsx file on your machine.
Step 7: Now click on run again to execute the next step. In this step, the VBA script will add contents to the range A2 to B10.
Once you have executed the step above, you will notice that the text “New Content Added” is repeated across A2 to B10, as shown below.
Step 8: Now execute the last step of the VBA script.
Once the code is executed, you will notice that the changes written to the workbook are saved and then closed.
Example #3
Let’s now look at an example where we will open and close the file using the workbook.close method using VBA. For this demo, we will create a blank Excel workbook and then run a VBA macro step-by-step.
Step 1: Create a blank Excel workbook DemoOpenClose.xlsx in your local folder, as shown below.
Step 2: On a Windows machine, use the Alt+F11 key to open a VBA editor for the Excel workbook DemoOpenClose.xlsx. This will open the VBA editor, as shown below.
Step 3: In the source workbook, navigate to the VBA screen. Right-click on the Modules and choose Insert à Module.
It will open a new macro window, as shown below. Click on the Sub Sub/UserForm run icon.
Step 4: Specify the Macro name in the Macros window. Click on the Create button.
Step 5: Specify the logic of your VBA script to open and close the file.
In this case, we have four steps to perform this logic. In Step 1, we opened the workbook by specifying the local path on your machine.
Following which, in Step 2, we have added content to the workbook.
Then, in Step 3, we saved the changes made to the workbook
In Step 4, we have finally closed the workbook with the saved changes.
We have added the below VBA code for this illustration.
Sub Workbook_Open_Close()
‘Open the workbook
Workbooks.Open Filename:=”C:\Users\<local file>\DemoOpenClose.xlsx”
‘Add contents to the file
Workbooks(“DemoOpenClose.xlsx”).Sheets(1).Range(“A2:C5”) = “Demo”
‘Save the changes
Workbooks(“DemoOpenClose.xlsx”).Save
‘Close the file
Workbooks(“DemoOpenClose.xlsx”).Close
End Sub
Step 6: If you run the above steps by clicking on the Run icon in the toolbar, you will notice that the workbook is saved with the contents as per your specifications.
Important Things to Note
- If you are closing a workbook from the VBA code, then no Auto_Close macros will be run in the workbook. It is recommended to use the RunAutoMacros method to run the Auto_Close macros.
- To ensure the changes are saved to the workbook, use the SaveChanges parameter set to True or False as per the requirement. By default the SaveChanges parameter is set to True.
- As a good practice, remember to include error handling to support any unpredictable behaviors during the script execution.
Frequently Asked Questions (FAQs)
If you use workbook close without specifying SaveChanges, it is set to True by default, which means any changes to the workbook will automatically be saved.
Yes, you can use a workbook.close to close all open workbooks. However, to do so you need to use a loop to iterate through all the open workbooks and close them using workbooks.close method.
To handle errors when closing a workbook in VBA, you may use the On Error GoTo Error Handler. This code will instruct the VBA to jump to the Error Handler. Also, it is recommended that you log all the errors to a file or worksheet and, where possible, provide a clear explanation of the error description so that a user can understand the nature of the issues.
To prompt the user to save changes before closing a workbook, you can perform two steps.
Step 1: Use the MsgBox method to prompt the user to save the workbook before closing
Step 2: Use the SaveChanges parameter to action based on the user inputs in step 1.
Recommended Articles
Guide to What Is VBA Workbook.Close. We learn how to use VBA Workbook.Close with examples and a working template. You can learn more from the following articles.
Leave a Reply