Microsoft Office Tutorials and References
In Depth Information
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 Wo rkbooks 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 Wo rkbooks
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
worksheets copied from existing workbooks. Regardless of what you want to use the new
workbook 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 Te mplate parameter, as in the following procedure:
Sub AddNewWorkbook()
Dim NewWbk As Workbook
Set NewWkbk = Workbooks.Add(Template:="C:\ExcelProg\MonthlySales.xls")
End Sub
Search JabSto ::




Custom Search