In this post, you will be learning about close and open functions in Excel VBA that shows how you can perform open/close actions on workbooks in Excel VBA.
The Close and Open Method is used to close and open workbooks in Excel VBA.
To run the codes,
- Under the developer tab, click visual basic
- Click the insert option and choose module
- Enter your codes and click run.
Close and Open WorkBook in Excel VBA
- First, place a command button on your worksheet.
- Under the developer tab, click insert and choose your command button.
- Save the workbook as “close and open methods in VBA.xlsm”.
In the code section, enter the following codes.
Workbooks("close and open methods in VBA.xlsm ").Close
This code line closes the close and open methods in VBA.xlsm file.
Workbooks(1).Close
This code line closes the first opened/created workbook.
ActiveWorkbook.Close
This code line closes the active workbook.
Workbooks.Close
This code line closes all workbooks that are currently open.
Workbooks.Open ("close and open methods in VBA.xlsm ")
This code line opens close and open methods in VBA.xlsm.
This command works only when the file is saved in the default location. If your file is in different location you can use the below code to open the standard open dialog box.
Dim MyFile As String MyFile = Application.GetOpenFilename()
The standard open dialog box opens, select a file and click Open.
If you know the actual directory but it’s hard to find the file. Follow the above procedure and enter the below code in the module and run it.
Workbooks.Open (file name)