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
Workbooks(“bi.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