The Sheets collection in Microsoft Office Interop Excel provides a flexible way to work with all worksheets and charts in a workbook. This guide explains the Sheets interface, its key properties and methods, and practical tips for using it in C# or other .NET languages. The primary keyword for this article is “Sheets collection” and it appears in the first paragraph to match search intent for informational queries about Excel interop programming.
Quick overview
The Sheets collection represents every sheet (worksheets and chart sheets) in the specified or active workbook. Use it when you need to handle sheets of mixed types or perform operations across multiple sheets.
Primary use cases:
- Enumerating all sheets in a workbook
- Adding, copying, moving, deleting sheets
- Accessing a specific sheet by name or index
- Applying operations to multiple sheets at once
Technical definition and context
- Namespace: Microsoft.Office.Interop.Excel
- Assembly: Microsoft.Office.Interop.Excel.dll
- Interface signature (conceptual): public interface Sheets : System.Collections.IEnumerable
The Sheets collection implements IEnumerable so you can iterate through it in a foreach loop. It may contain Worksheet or Chart objects; use it when you want a general collection that can include both.
Key properties
- Application — returns the Excel Application object (read-only). Use this to access application-level settings.
- Count — returns the number of sheets in the collection (Integer, read-only).
- Creator — 32-bit integer that indicates the application in which the object was created (e.g., “XCEL”).
- HPageBreaks — horizontal page breaks for the sheet (read-only).
- VPageBreaks — vertical page breaks for the sheet (read-only).
- Item(index) — returns a single sheet object by name or index.
- Parent — returns the parent object (typically the Workbook).
- Visible — read/write property to show or hide a sheet.
Example (access by index or name):
- Sheets returns the first sheet.
- Sheets[“Sheet2”] returns the sheet named “Sheet2”.
Important methods
- Add(Object, Object, Object, Object) — create a new worksheet, chart, or macro sheet. The new sheet becomes active.
- Copy(Object, Object) — copy a sheet to another workbook or location.
- Delete() — delete the sheet.
- FillAcrossSheets(Range, XlFillWith) — copy a range to the same area on all sheets in a collection.
- Move(Object, Object) — move the sheet within the workbook.
- PrintOut(…) / PrintOutEx(…) — print one or multiple sheets.
- PrintPreview(Object) — show print preview.
- Select(Object) — select one or multiple sheets.
- GetEnumerator() — obtain an enumerator for iteration.
Note: Add2 is implemented only for Charts collection and will throw a runtime error if used with Sheets or Worksheets.
Typical code patterns (C#)
Iterate sheets:
- foreach (Excel.Worksheet sh in workbook.Sheets) { / use sh / }
Add a worksheet:
- workbook.Sheets.Add(Type.Missing, workbook.Sheets[workbook.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
Copy a sheet:
- workbook.Sheets[“Sheet1”].Copy(Type.Missing, workbook.Sheets[workbook.Sheets.Count]);
Delete a sheet (be careful; consider DisplayAlerts = false to suppress prompts):
- excelApp.DisplayAlerts = false;
- workbook.Sheets[“SheetToDelete”].Delete();
- excelApp.DisplayAlerts = true;
Fill a range across sheets:
- workbook.Sheets.FillAcrossSheets(range, Excel.XlFillWith.xlFillDefault);
Practical tips and gotchas
- Mixed sheet types: The Sheets collection can include Chart objects. If you expect only worksheets, prefer the Worksheets collection to avoid runtime type issues.
- Indexing: COM interop indexing is 1-based. Sheets is the first sheet.
- Release COM objects: Always release COM references (Marshal.ReleaseComObject) and call GC.Collect/G C.WaitForPendingFinalizers to avoid Excel processes remaining in memory.
- Security and permissions: Operations requiring file access or interop may require appropriate permissions on the host machine.
- Performance: Batch operations across multiple sheets (for example, FillAcrossSheets) tend to be faster than looping and performing the same action per sheet.
- DisplayAlerts: When deleting or overwriting sheets programmatically, set Application.DisplayAlerts = false to suppress confirmation dialogs, then restore it.
When to use Sheets vs Worksheets
Use Sheets when:
- You need to work with both worksheets and chart sheets.
- You must perform actions across all sheet types.
Use Worksheets when:
- You only need worksheets and want type safety (avoids chart objects).
- You prefer clearer intent in code.
Example scenario
Task: Copy a template range from the active sheet to the same range on all other worksheets.
Approach:
- Identify the source Range object on the active sheet.
- Call workbook.Sheets.FillAcrossSheets(sourceRange, Excel.XlFillWith.xlFillCopy);
This uses FillAcrossSheets to efficiently replicate the range to the same area on every sheet in the collection.
E-E-A-T and best practices for implementation
- Expertise: Follow Microsoft Interop documentation and use strongly typed references for clarity.
- Experience: Prefer Worksheets when only worksheet operations are required; use Sheets for mixed collections.
- Authoritativeness: Test scripts on sample workbooks before deploying to production to prevent data loss.
- Trustworthiness: Always back up workbooks before performing destructive actions (Delete, Move, Copy that can overwrite).
References
- Microsoft Docs — Sheets (Microsoft.Office.Interop.Excel)
- Microsoft Docs — Worksheets and Sheets collections
- Best practices for Office Interop: manage COM objects and application lifetime