An Excel Workbook is a file with the extension .xls or .xlsx .

( .xls for Excel 97 through 2003, .xlsx for Excel 2007 and greater).

The basic building block of Excel starts with the workbook.

[See the Excel object model]

Declare a variable for an Excel workbook

SET the workbook variable to an Existing workbook

SET the workbook variable to the ActiveWorkbook workbook

Set the workbook variable to a New workbook

Closing a Workbook

Saving a Workbook

Example: Open an existing workbook in VBA

Example: Open a new workbook in VBA

Referencing Objects in a Workbook

The Workbooks Collection

All workbook objects are part of the Workbooks Collection of all open Excel workbooks.

Creating or opening a specific workbook must reference Workbooks - plural.

The Workbooks Collection has only a few Methods, the primary ones being: Add, Copy, Delete, Move, and Select. You Open, Add, and Close an individual workbook from the workbooks collection object.

You will see this in the examples below.

The Workbooks Collection has very few Properties. The ones you will use most are Count and Item.

The variable x will contain the number of open Workbooks.

The Item property is used to reference a specific workbook.

'The word Item is often left out.

The Workbook Object

Declare a variable for an Excel workbook.

Put this declaration at the beginning of your VBA program.

You give your workbook a variable name. (In this example wkb).

This allows you to reference the workbook in your code by name.

You can use whatever name you want, but a shorter name will be easier to type,
which you will do often in the code, and you should use a name that reminds you of what it means.

Sometimes you will have more than one workbook open, and you will need to give them separate names.

SET the workbook variable to an Existing workbook.

The SET statement is used to assign the variable to a specific workbook.

In this example, the SET statement references a workbook by name and also Opens the workbook.

Notice that the SET statement Opens a workbook from the workbooks collection.

For the rest of the program we can refer to this workbook by its name wkb

SET the workbook variable to the ActiveWorkbook workbook.

If the VBA code is within a specific workbook, we can reference it as follows:

This works if you only have one workbook open, which is quite often.

Set the workbook variable to a New workbook.

This example uses the SET statement to create a NEW workbook.

A New Excel workbook is opened. It has no name until we assign one.

Notice that the new workbook is added to the Workbooks collection.

Closing a workbook

To close a workbook, we use the Close method with our workbook variable name.

If we reference the workbooks collection then ALL the Open workbooks are closed.

It's not possible to perform a Save on the Workbooks Collection. They must be saved individually.

Saving a Workbook

Either of these samples will Save the workbook.

To save an existing Excel file:

You don't give the name of the file with Save.

To save and name a new or existing file (SAVEAS):

SaveAs will assign a name and path. Use this to save a new file or to rename an existing one.

You can also assign a workbook name when you use the Close method.

These are the methods you will use the most often with a Workbook.

In later examples you will see more advanced methods of the Workbook.

* * * * * * * * * * * * * * * * * * * * * * * * * *

Putting It Together:

Open an existing workbook in VBA

'Declare a variable for the workbook

'Set the variable to an existing workbook

'You can now reference a worksheet in the wkb workbook

'Here is where you will place code to work with the worksheets

'Save and then Close the workbook

Open a new workbook in VBA

This is the same as the above example except for the SET statement and the SAVEAS statement.

'Set the variable to the Workbooks collection.

'Use the command Add to open a new workbook.


'Here is where you will place code to work with the worksheets


'Use SaveAs and name the new workbook. Then close it.

Referencing Objects in a Workbook

In both of these example, the workbook will be refered in the VBA code as wkb, which is the variable assigned to it.

This will be used to reference objects within the Workbook, such as Worksheets.

For Example:

In this example, the workbook variable wkb references a specific worksheet in the worksheets collection.

See Excel Worksheets to see how to work with individual worksheets.

Copyright © 2006-2016, LQSystems,Inc. All rights reserved.

   Printer Friendly Page