Skip to Main Content
Excel® 2007 VBA Programmer's Reference
book

Excel® 2007 VBA Programmer's Reference

by John Green, Stephen Bullen, Rob Bovey, Michael Alexander
March 2007
Intermediate to advanced content levelIntermediate to advanced
1173 pages
26h 1m
English
Wrox
Content preview from 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" ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Excel® 2007 Power Programming with VBA

Excel® 2007 Power Programming with VBA

John Walkenbach
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 9780470046432Purchase book