0 out of 1 rated this helpful - Rate this topic

How to: Programmatically List All Worksheets in a Workbook

Visual Studio 2012

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

Applies to: The information in this topic applies to document-level projects and application-level projects for Excel 2013 and Excel 2010. 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

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

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 Microsoft.Office.Interop.Excel.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++;
    }
}

Tasks

How to: Programmatically Add New Worksheets to Workbooks

How to: Programmatically Move Worksheets Within Workbooks

Concepts

Working with Worksheets

Global Access to Objects in Office Projects

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)