Programmatically list all worksheets in a workbook

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

Applies to: The information in this topic applies to document-level projects and VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

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

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

   C#

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

   VB

   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
   

To list all existing worksheets in a workbook in a VSTO Add-in

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

   C#

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

   VB

   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
   

Related content

• Work with worksheets
• How to: Programmatically add new worksheets to workbooks
• How to: Programmatically move worksheets within workbooks
• Global access to objects in Office projects