Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath

2017-07-07 02:10:07

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

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.