Description:

Sometimes we may want to open and close an existing workbook using VBA. You can set the opened workbook to an object, so that it is easy to refer your workbook to do further tasks.

Open Excel Workbook using VBA

The following Macros will allow to open an esisting workbook using Open method.

Open Esisting Workbook in Excel VBA – Example Cases:

Open existing workbook

You can use the following code to Open an existing Workbook and save in the C drive using Open method.

Code:
Sub Open_ExistingWorkbook()
Workbooks.Open "C:\WorkbookName.xls"
'OR
'Workbooks.Open Filename:="C:\WorkbookName1.xls"
End Sub
Output:

You should see opened workbook along with existing workbook.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see opened workbook
  7. Save the file as macro enabled workbook
Set opened workbook to an object

You can set the Object to opened workbook, so that it is easy to refer to your workbook to do further tasks.

Code:
Sub Set_Open_ExistingWorkbook()
Dim wkb As Workbook
Set wkb = Workbooks.Open("C:\WorkbookName.xls")
'OR
Set wkb = Workbooks.Open(Filename:="C:\WorkbookName1.xls")
End Sub
Output:

You should see newly opened workbook along with existing workbook.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see opened workbook
  7. Save the file as macro enabled workbook

Close Excel Workbook using VBA

Close an Opened Workbook using VBA
You can use Close Method of workbook to close an opened workbook in Excel using VBA.

Close an Opened Workbook – Example Cases:

Close an Opened Workbook

You can use close method in the following way.The following code will close an opened Workbook.

Code:
Sub ExampleCloseWorkbook()
'Procedure to close an Excel worbook
'Close is an workbook method to close a workbook
ActiveWorkbook.Close
End Sub
Output:

When ever we run the above macro automatically the file will get closed.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute the procedure
  7. The file get closed, You can’t see the file
Close an Opened Workbook using object

You can set the Object to open workbook, so that it is easy to refer to your workbook to do further tasks. Using same object you can close an opened workbook.

Code:
Sub ExampleOpenAnExistingWorkbookSet()
'Declaration - declare an object for setting the workbook
Dim wkb As Workbook
'Open Workbook and set an object(wkb)
Set wkb = Workbooks.Open("C:\WorkbookName.xls")
'Close opened workbook using object
wkb.Close SaveChanges:=True
End Sub
Output:

When ever we run the above macro automatically the file will get closed.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute the macro
  7. The file has closed, You can’t see the file

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!