Writing Excel Macros with VBA, 2nd Edition

Chapter 17. The Workbook Object

In this chapter, we discuss the Workbook object and the Workbooks collection. Figure 17-1 shows the portion of the Excel object model that relates directly to workbooks.

Figure 17-1. The Workbook object

The Workbooks Collection

The Application object has a Workbooks property that returns a Workbooks collection, which contains all of the Workbook objects for the currently open instance of Excel. For instance, the following code displays the number of open workbooks:

Dim wbs As Workbooks
Set wbs = Application.Workbooks
MsgBox wbs.Count

Let us look at a few of the properties and methods of the Workbooks collection.

Add Method

The Add method creates a new workbook, which is then added to the Workbooks collection. The new workbook becomes the active workbook. The syntax is:

                  WorkbooksObject.Add(Template)

where the optional Template parameter determines how the new workbook is created. If this argument is a string specifying the name of an existing Excel template file, the new workbook is created with that file as a template.

As you may know, a template is an Excel workbook that may contain content (such as row and column labels), formatting, and macros and other customizations (menus and toolbars, for instance). When you base a new workbook on a template, the new workbook receives the content, formatting, and customization from the template.

The Template ...