Excel 2003 VBA Programmer's Reference

18

Workbooks and Worksheets

Chapter 18 is about programmaticaUy managing workbooks and worksheets. In this chapter, you will learn about important, fundamental tasks that will aid you in effectively managing these two basic Excel objects. Specifically, we will look at workbook and worksheet collections and the code necessary to save and overwrite these core objects as their state evolves.

Using the Workbooks Collection

The Workbooks collection is a property of the Application object. Workbooks represent all of the open Workbooks open for each instance of Excel running. Like all collections, Workbooks is simply designed as a place to put Workbook objects. Also, like all collections you can add or remove items from the collection. Let's take a moment to look at some of the programmatic gymnastics we can perform on the Workbooks collection and Workbooks in general.

As the name suggests, a collection is just a place to collect things. All collections are pretty much the same: things are added, counted, and removed. It is the objects in the collections that provide a rich feature set. Thus, most of the cool things we'll see next are actually operations performed on individual Workbook objects.

Creating a New Workbook

A basic idea behind a good object model is to provide features that are consistent and intuitive. For example, if we have a collection of things then it is intuitive that we should be able to add things to the collection. In fact, it is intuitive that all collections support ...