How To Use VBA Close and Open workbooks In Excel

Oct 22, 2020 • edited Oct 23, 2020

How To Use VBA Close and Open workbooks In Excel

The Close and Open Method in Excel VBA can be used to close and open workbooks. Remember, the Workbooks collection contains all the Workbook objects that are currently open.

Place a command button on your worksheet and add the following code lines:

1. The code line below closes close-open.xlsm.

          Workbooks("close-open.xlsm").Close

2. The code line below closes the first opened/created workbook.

          Workbooks(1).Close

3. The code line below closes the active workbook.

          ActiveWorkbook.Close

4. The code line below closes all workbooks that are currently open.

          Workbooks.Close

5. The code line below opens sales.xlsx.

          Workbooks.Open ("sales.xlsx")

Note: you can only open sales.xlsx without specifying the file's path if it's stored in your default file location. To change the default file location, on the File tab, click Options, Save.

6. You can also use the GetOpenFilename method of the Application object to display the standard Open dialog box.

          Dim MyFile As String

          MyFile = Application.GetOpenFilename()

7. Select a file and click Open.

GetOpenFilename Method

Note: GetOpenFilename doesn't actually open the file.

8. Next, you can open the workbook as usual.

          Workbooks.Open (MyFile)
#Tutorial#How To#VBA#Workbook#Worksheet

How To Use VBA Select The Current Region In Excel

How To Use VBA Combo Box In Excel