Working with the Workbooks Collection

The Workbooks collection, available from the Application object's Workbooks property, contains a collection of the Workbook objects that are 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 never have to call the GetEnumerator method directly because the For Each keyword in Visual Basic uses this method to iterate over a collection of Workbooks. See Listing 5.8 for an example of using For Each.

Listing 5.8. A VSTO Customization That Iterates over the Workbooks Collection Using For Each

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim workbooks As Excel.Workbooks = Me.Application.Workbooks     For Each workbook As Excel.Workbook In workbooks       MsgBox(workbook.Name)     Next   End Sub End Class 

Accessing a Workbook in the Workbooks Collection

To access a Workbook in the Workbooks collection, you use the Item property, which returns a Workbook object. The Item property has an Index parameter that is of type Object. You can pass an Integer representing the 1-based index of the Workbook in the collection you want to access. (Almost all collections in the Office object models are 1-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 workbooktypically, Book1, with no file extension.

Because Item is the default property of a collection, you can omit actually writing out the property name Item and instead just pass the Index parameter; you can write code like workbooks(1) instead of workbooks.Item(1). Listing 5.9 shows an example of calling Item with both kinds of indexing and omitting writing the property name Item by using the default property feature of Visual Basic.

Listing 5.9. A VSTO Customization That Gets a Workbook Using Item (the Default Property) with an Integer and String Index

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim workbooks As Excel.Workbooks = Me.Application.Workbooks     If workbooks.Count > 0 Then       ' Get the first workbook in the collection (1-based)       Dim wb As Excel.Workbook = workbooks(1)       MsgBox(wb.Name)       ' Get the same workbook by passing name of workbook       Dim wb2 As Excel.Workbook = workbooks(wb.Name)       MsgBox(wb2.Name)     End If   End Sub End Class 

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, 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

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim workbooks As Excel.Workbooks = Me.Application.Workbooks     ' Create a new workbook using mytemplate.xls as a template     Dim workbook1 As Excel.Workbook = workbooks.Add("c:\mytemplate.xls")     ' Create a new workbook with one chart sheet     Dim workbook2 As Excel.Workbook     workbook2 = workbooks.Add(Excel.XlWBATemplate.xlWBATChart)     ' Set default number of new sheets to create in a     ' new blank workbook to 10     Me.Application.SheetsInNewWorkbook = 10     ' Create a blank workbook with 10 worksheets     Dim workbook3 As Excel.Workbook = workbooks.Add()   End Sub End Class 

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 parameter: a String representing the filename of the workbook to open. It also has 14 optional parameters, which you can omit if you do not want to use any of them. 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

Public Class ThisWorkbook   Private Sub ThisWorkbook_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim workbook As Excel.Workbook     Workbook = Me.Application.Workbooks.Open("c:\myworkbook.xls")     MsgBox(workbook.Name)   End Sub End Class 

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 click 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.