Page 155 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model

Chapter 7

Workbooks and Worksheets

The Workbooks Collection . . . . . . . . . . 129 The Sheets and Worksheets
Collections . . . . . . . . . . . . . . . . . . . . . . . 149

The workbook is the highest level of organization within Microsoft Excel, so you might think
that there aren’t a whole lot of actions you can take on a workbook beyond creating new
workbooks, saving changes, closing workbooks, or deleting workbooks you no longer need.
While it is true that most “workbook” manipulations actually occur at the worksheet and cell
level, you’ll still find plenty to do with workbooks. This chapter also discusses worksheets,
both as worksheets and as members of the Sheets collection, so you’ll find the resources you
need to create workbooks and set them up the way you want them (in terms of password pro­
tection, the number of worksheets, and the names of those worksheets) before you start
manipulating the values contained in them.

The Workbooks Collection

The Workbooks collection contains references to every workbook that you have open in your
copy of Excel. If there’s some change you want to make to every open workbook, you can use
a For Each…Next loop to move through the collection and make those changes. The Workbooks
collection contains a number of other useful methods that you can use to manipulate your
existing workbooks, but the most basic ability is that of creation—you need to be able to cre­
ate a new workbook before you can manipulate it.

Creating New Workbooks
One of the basic tasks you’ll want to complete when you program Excel is to create a new
workbook. New workbooks can be the repository of new information or the target of work-
sheets copied from existing workbooks. Regardless of what you want to use the new work-
book for, you can create the workbook using the following code:

Workbooks.Add
If you want to create a workbook that’s a copy of an existing workbook, you can do so by set­
ting the Add method’s Template parameter, as in the following procedure:

Sub AddNewWorkbook()
Dim NewWbk As Workbook
Set NewWkbk = Workbooks.Add(Template:="C:\ExcelProg\MonthlySales.xls")
End Sub

129