Working with the Workbooks Collection

The Workbooks collection, available from the Application object's Workbooks property, contains a collection of the Workbook objects currently open in the application. It also has methods used to manage open workbooks, create new workbooks, and open existing workbook files.

Iterating over the Open Workbooks

Collections implement a special method called GetEnumerator that allows them to be iterated over. You do not ever have to call the GetEnumerator method directly because the foreach keyword in C# uses this method to iterate over a collection of Workbooks. See Listing 5-8 for an example of using foreach.

Listing 5-8. A VSTO Customization That Iterates over the Workbooks Collection Using foreach

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Workbooks workbooks = this.Application.Workbooks; foreach (Excel.Workbook workbook in workbooks) { MessageBox.Show(workbook.Name); } }

Accessing a Workbook in the Workbooks Collection

To access a Workbook in the Workbooks collection, you use the get_Item method, which returns a Workbook object. The get_Item method has an Index parameter that is of type object. You can pass an int representing the one-based index of the Workbook in the collection you want to access. (Almost all collections in the Office object models are one-based.)

Alternatively, you can pass a string representing the name of the Workbook you want to access. The name for a workbook is the name of the file if it has been saved (for example, "Book1.xls"). If the workbook has not yet been saved, it will be the temporary name that Excel creates for a new workbook, typically Book1 with no file extension. Listing 5-9 shows an example of calling get_Item with both kinds of indexing.

Listing 5-9. A VSTO Customization That Gets a Workbook Using get_Item with an int and string Index

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Workbooks workbooks = this.Application.Workbooks; if (workbooks.Count > 0) { // Get the first workbook in the collection (1-based) Excel.Workbook wb = workbooks.get_Item(1); MessageBox.Show(wb.Name); // Get the same workbook by passing the name of the workbook. Excel.Workbook wb2 = workbooks.get_Item(wb.Name); MessageBox.Show(wb2.Name); } }

You can also use the Workbooks collection's Count property to determine the number of open workbooks. You should check the Count property before accessing a workbook by index to make sure your index is within the bounds of the collection.

Creating a New Workbook

To create a new workbook, you can use the Workbooks collection's Add method. The Add method returns the newly created Workbook object. It takes as an optional parameter an object that can be set to a string specifying the filename of an existing workbook to use as a template. Alternatively, you can pass a member of the XlWBATemplate enumeration (xlWBATChart or xlWBATWorksheet) to specify that Excel should create a workbook with a single chart sheet or a single worksheet. If you omit the parameter by passing Type.Missing, Excel will create a new blank workbook with the number of worksheets specified by Application.SheetsInNewWorkbook property. Listing 5-10 shows several ways to create a new workbook.

Listing 5-10. A VSTO Customization That Creates New Workbooks Using Workbooks.Add

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Workbooks workbooks = this.Application.Workbooks; // Create a new workbook using mytemplate.xls as a template Excel.Workbook workbook1 = workbooks.Add( @"c:mytemplate.xls"); // Create a new workbook with one chart sheet Excel.Workbook workbook2 = workbooks.Add( Excel.XlWBATemplate.xlWBATChart); // Set default number of new sheets to create in a // new blank workbook to 10 this.Application.SheetsInNewWorkbook = 10; // Create a blank workbook with 10 worksheets Excel.Workbook workbook3 = workbooks.Add(missing); }

Opening an Existing Workbook

To open an existing workbook, you can use the Workbooks collection's Open method, which returns the opened Workbook object. Open has one required parametera string representing the filename of the workbook to open. It also has 14 optional parameters for which you can pass Type.Missing if you do not want to use any of these parameters. Listing 5-11 shows the simplest possible way of calling the Open method.

Listing 5-11. A VSTO Customization That Opens a Workbook Using the Workbooks.Open Method

private void ThisWorkbook_Startup(object sender, EventArgs e) { Excel.Workbook workbook = this.Application.Workbooks.Open( @"c:myworkbook.xls", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); MessageBox.Show(workbook.Name); }

Closing All the Open Workbooks

Excel provides a Close method on the Workbooks collection to close all the open workbooks. The user is prompted to save any unsaved workbooks unless Application.DisplayAlerts is set to false. As with Application.Quit, you cannot be guaranteed that all the workbooks will actually be closed because the user can press the Cancel button when prompted to save a workbook and other event handlers that are loaded in Excel from other add-ins can handle the BeforeClose event and set the cancel parameter to true.