Working with the Worksheets, Charts, and Sheets Collections

The Worksheets, Charts, and Sheets collections are very similar, so this section covers them together. They differ mainly in whether they contain worksheets (Worksheets), chart sheets (Charts), or both (Sheets). In this section, as in the rest of the chapter, we use the word sheet to refer to either a chart sheet or a worksheet.

Iterating over the Open Sheets

These collections have a GetEnumerator method that allows them to be iterated over using a For Each loop in Visual Basic, as shown in Listing 5.13.

Listing 5.13. A VSTO Customization That Iterates over the Worksheets, Charts, and Sheets Collections

Public Class ThisWorkbook   Private Sub ThisWorkbook_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim app As Excel.Application = Me.Application     Me.Charts.Add()     Dim sheet As Excel.Worksheet     For Each sheet In Me.Worksheets       MsgBox(String.Format("Worksheet {0}", sheet.Name))     Next     Dim chart As Excel.Chart     For Each chart In Me.Charts       MsgBox(String.Format("Chart {0}", chart.Name))     Next     Dim objSheet As Object     For Each objSheet In Me.Sheets       If TypeOf (objSheet) Is Excel.Worksheet Then         MsgBox(String.Format("Worksheet {0}", objSheet.Name))       End If       If TypeOf (objSheet) Is Excel.Chart Then         MsgBox(String.Format("Chart {0}", objSheet.Name))     Next      End If   End Sub End Class 

Accessing a Sheet in the Collection

To access a sheet in the Worksheets, Charts, and Sheets collections, you use a property called Item, which returns an Object. You need to cast the returned Object to a Worksheet or Chart. Objects returned from the Worksheets collection can always be cast to Worksheet. Objects returned from the Charts collection can always be cast to Chart. Objects returned from the Sheets collection should be tested using the TypeOf operator to determine whether the Object returned is a Worksheet or a Chart. It can then be cast to a Worksheet or a Chart.

The Item property takes an Index parameter of type Object. You can pass a String representing the name of the worksheet or chart sheet, or you can pass a 1-based index into the collection. You can check how many items are in a given collection by using the Count property.

Adding a Worksheet or Chart Sheet

To add a worksheet or chart sheet to a workbook, you use the Add method. The Add method on the Sheets and Worksheets collection takes four optional parameters of type Object: Before, After, Count, and Type. The Charts collection Add method takes only the first three parameters.

The Before parameter can be set to a Worksheet or Chart representing the sheet before which the new sheet is to be added. The After parameter can be set to the Worksheet or Chart representing the sheet after which the new sheet is to be added. The Count parameter can be set to the number of new sheets you want to add. The Type parameter is set to XlSheetType.xlWorksheet to add a worksheet or XlSheetType.xlChart to add a chart sheet. Note that if you try to use xlChart as the Type parameter when using Worksheets.Add, Excel will throw an exception because Worksheets is a collection of only Worksheet objects. You can specify either Before or After, but not both parameters. If you omit the Before and After parameters, Excel adds the new sheet after all the existing sheets.

Listing 5.14 shows several ways of using the Add method on the various collections.

Listing 5.14. A VSTO Customization That Uses the Add Method on the Charts, Sheets, and Worksheets Collections

Public Class ThisWorkbook   Private Sub ThisWorkbook_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim chart1 As Excel.Chart = Me.Charts.Add()     Dim chart2 As Excel.Chart     chart2 = Me.Sheets.Add(Type:=Excel.XlSheetType.xlChart)     Dim sheet1 As Excel.Worksheet     sheet1 = Me.Sheets.Add(chart1, Count:=3)     Dim sheet2 As Excel.Worksheet     sheet2 = Me.Worksheets.Add(After:=chart2)   End Sub End Class 

Copying a Sheet

You can make a copy of a sheet by using the Copy method, which takes two optional parameters: Before and After. You can specify either Before or After, but not both parameters.

The Before parameter can be set to a Worksheet or Chart representing the sheet before which the sheet should be copied to. The After parameter can be set to a Worksheet or Chart representing the sheet after which the new sheet should be copied to. If you omit the Before and After parameters, Excel creates a new workbook and copies the sheet to the new workbook.

Moving a Sheet

The Move method moves the sheet to a different location in the workbook (that is, it moves the sheet to a different tab location in the worksheet tabs) and has two optional parameters: Before and After. You can specify either Before or After, but not both parameters. If you omit both parameters, Excel creates a new workbook and moves the sheet to the new workbook.