Excel VBA: Open and Close Multiple Excel Workbooks Using Command Buttons

Welcome to Excel Jet Consult. In this tutorial, we will learn how to automatically open and close multiple Excel workbooks with a single click. Let’s get started

Below are the two Excel workbooks we want to write VBA code and assigned to Command Buttons which will automatically open

Note, the two workbooks is located in the 3D Objects on my computer which is important to note and properly referenced when writing the codes. In addition, the names of each excel workbook are bi and SUMIFS with Group Sheet.

So, we are going to open a new workbook where we will write the codes in the VBA Editor

We want to insert to Command Buttons. In each of the command buttons, we will write codes that will open and close workbooks respectively

To insert Command Buttons: In the Controls group of the Developer tab, click on the Insert drop-down and select Command Button (ActiveX Control) and create two button in the spreadsheet as seen below

Double click on the CommandButton1 to open the code window and execute the following lines of code

Workbooks.Open “C:\Users\ABIOLA DAVID\3D Objects/SUMIFS with Group Sheet.xlsx”

Workbooks.Open “C:\Users\ABIOLA DAVID\3D Objects/bi.xlsx”

Each of the lines of code contains the path located, the names of the Excel workbook and extension which is .xlsx

We need to close the VBA editor to return to Excel. Next, double-click on the CommandButton2 to supply the codes that will automatically close the workbooks.

In the code window, execute the codes

Workbooks(“SUMIFS with Group Sheet.xlsx”).Close


Close the VBA Editor to return to Excel

To rename the CommandButton1 and 2, right-click on each of them and select Properties

Delete the CommandButton1 in the Caption and Replace with whatever caption desired such as Open Workbooks and Click Enter

Repeat the same steps for CommandButton2 and renamed Close Workbooks

Finally, click on the Design Mode. Ready to use

To open the two workbooks, click on the Open Workbook Button

To close the workbooks, simply click on the Close Workbook button. Hope you enjoy the tutorial. Kindly comment below if found useful

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *