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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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
LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference