Page 61 - Excel Progamming Weekend Crash Course

d540629 ch03.qxd 9/2/03 9:27 AM Page 36

36 Friday Evening

The Workbook Object

Each open workbook in Excel is represented by a Workbook object, which is maintained in
the Workbooks collection of the Application object. This section shows you how to per-
form various important tasks with a Workbook object.

Creating and Opening Workbooks
You use the Workbooks collection’s Add method to create a new workbook. The syntax is:

Workbooks.Add(Template)

Because the Workbooks collection is a child of the Application object, the
full syntax for this would be:
Note Application.Workbooks.Add(Template)
When programming within Excel, however, Application is always available as
an implicit reference, so it does not need to be specified although it can be.
Template is an optional argument specifying the name of an existing workbook file (on
disk). If the argument is included, a new workbook is created based on the existing work-
book. If the argument is omitted, a new workbook is created containing three empty work-
books. The method returns a reference to the newly created workbook, which can be used
or ignored. For example:

Dim MyWB As Workbook
Set MyWB = Workbooks.Add

As always, the Set keyword is used because the statement assigns an object reference.
The variable MyWB refers to the new workbook, and can be used to manipulate it. A refer-
ence to the workbook also exists in the Workbooks collection.
To open an existing workbook from disk, use the Open method:
Workbooks.Open(Filename)

The Filename argument is the name of the workbook file to open, including the full path
if necessary. This method opens the specified workbook, adds it to the Workbooks collec-
tion, and returns a reference to the workbook. Here’s an example:
Dim MyWB As Workbook
Set MyWB = Workbooks.Open(“c:\data\sales.xls”)
If the specified file does not exist or cannot be opened for any other reason (such as a
sharing violation), a runtime error occurs.

For more about dealing with this and other kinds of errors, see Session 27.

Cross-Ref