How to: Programmatically list all worksheets in a workbook

  • Article

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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.

    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++;
    }
}

    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.

    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++;
    }
}

    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

See also