How to: List All Worksheets in a Workbook

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Document-level projects Application-level projects Microsoft Office version Excel 2003 Excel 2007 For more information, see Features Available by Application and Project Type.

The Workbook class provides a Worksheets object. This object contains a collection of all the Worksheet objects in the workbook.

To list all existing worksheets in a workbook in a document-level customization

• Iterate through the Worksheets collection and send the name of each sheet to a cell offset from a NamedRange control.

  Private Sub ListSheets()
      Dim index As Integer = 0
  
      Dim NamedRange1 As Microsoft.Office.Tools.Excel.NamedRange = _
          Globals.Sheet1.Controls.AddNamedRange( _
          Globals.Sheet1.Range("A1"), "NamedRange1")
  
      For Each displayWorksheet As Excel.Worksheet In Globals.ThisWorkbook.Worksheets
          NamedRange1.Offset(index, 0).Value2 = displayWorksheet.Name
          index += 1
      Next displayWorksheet
  End Sub
  

  private void ListSheets()
  {
      int index = 0;
  
      Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
          Globals.Sheet1.Controls.AddNamedRange(
          Globals.Sheet1.Range["A1", missing], "NamedRange1");
  
      foreach (Excel.Worksheet displayWorksheet in Globals.ThisWorkbook.Worksheets)
      {
          NamedRange1.Offset[index, 0].Value2 = displayWorksheet.Name;
          index++;
      }
  }
  

To list all existing worksheets in a workbook in an application-level add-in

• Iterate through the Worksheets collection and send the name of each sheet to a cell offset from a Range object.

  Private Sub ListSheets()
      Dim index As Integer = 0
  
      Dim rng As Excel.Range = Me.Application.Range("A1")
  
      For Each displayWorksheet As Excel.Worksheet In Me.Application.Worksheets
          rng.Offset(index, 0).Value2 = displayWorksheet.Name
          index += 1
      Next displayWorksheet
  End Sub
  

  private void ListSheets()
  {
      int index = 0;
  
      Excel.Range rng = this.Application.get_Range("A1", missing);
  
      foreach (Excel.Worksheet displayWorksheet in this.Application.Worksheets)
      {
          rng.get_Offset(index, 0).Value2 = displayWorksheet.Name;
          index++;
      }
  }
  

See Also

Tasks

How to: Add New Worksheets to Workbooks

How to: Move Worksheets Within Workbooks

Concepts

Working with Worksheets

Global Access to Objects in Visual Studio Tools for Office Projects