Excel® 2007 VBA Programmer's Reference

Chapter 3. Workbooks and Worksheets

In this chapter, you learn how to create new Workbook objects and how to interact with the files that you use to store those workbooks. To do this, some basic utility functions are presented. You also see how to handle the Sheet objects within the workbook, and how some important features must be handled through the Window object. Finally, you learn how to synchronize your worksheets as you move from one worksheet to another.

The Workbooks Collection

The Workbooks collection consists of all the currently open Workbook objects in memory. Members can be added to the Workbooks collection in a number of ways. You can create a new empty workbook based on the default properties of the Workbook object, or you can create a new workbook based on a template file. Finally, you can open an existing workbook file.

To create a new empty workbook based on the default workbook, use the Add method of the Workbooks collection:

Workbooks.Add

The new workbook will be the active workbook, so you can refer to it in the following code as ActiveWorkbook. If you immediately save the workbook, using the SaveAs method, you can give it a filename that can be used to refer to the workbook in later code, even if it is no longer active. Before you try the following code, make sure you have a C:\Data directory or change the directory name used in the code:

Workbooks.Add ActiveWorkbook.SaveAs Filename:="C:\Data\SalesData1.xlsx" Workbooks.Add ActiveWorkbook.SaveAs Filename:="C:\Data\SalesData2.xlsx" ...