O'Reilly logo

Writing Excel Macros with VBA, 2nd Edition by Steven Roman PhD

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

The Workbook object

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 argument ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required