CODEWITHSUNDEEP
c#excel
ardmore
ardmore

Reputation: 177

How can I determine how many worksheets there are in an Excel workbook?

thanks in advance for your help. I want to loop through all worksheets in a workbook. Unfortunately, I don't know how many worksheets there are in a given workbook. Right now I use the following technique to enumerate through all worksheets:

Excel.Worksheet xlWorkSheet1;
xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Worksheet xlWorkSheet2;
xlWorkSheet2 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
Excel.Worksheet xlWorkSheet3;
xlWorkSheet3 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3);

Is there a method that returns the number of worksheets in a workbook?

Upvotes: 4

Views: 27154

Answers (6)

kieshi 48
kieshi 48

Reputation: 1

I use this code:


//Create application.
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();

//Opening excel book
Workbook ObjWorkBook = ObjExcel.Workbooks.Open(excelFileLocation, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);

//Getting sheets count
int sheetsCount = ObjWorkBook.Worksheets.Count;

For this code to work, you need to install the package using NuGet - Microsoft.Office.Interop.Excel

Upvotes: 0

Diego
Diego

Reputation: 57

We can now use: int Workbook.Sheets.Count

Upvotes: 0

Vickar
Vickar

Reputation: 953

Insert a module in the workbook you want to count the total sheets of, Then type the below code and hit run

Public Sub CountWorksheets()
       MsgBox "Total Sheets count:" & Application.Sheets.Count

End Sub

You'll get a relevant output like below

output

Upvotes: 0

Kees Boon
Kees Boon

Reputation: 1

Googling this results in either this page, or several others where you have to use VBA or C# or start mucking about in the Name Manager in Excel.

Anyone looking for a quick and dirty solution:

Open the xlsx file with 7zip and navigate to xl\worksheets. In there you should see all the sheets, and if you select those 7zip will count them for you.

Upvotes: 0

Mengo
Mengo

Reputation: 210

The Worksheets property has a collection of the worksheets .

foreach (Excel.Worksheet xlworksheet in xlworkbook.Worksheets)
{
    //xlworksheet code here
}

Or, get the count value and use a for loop.

Upvotes: 0

Neil Knight
Neil Knight

Reputation: 48597

Use Excel.Workbook and then you can use the Workbook.Sheets.Count() in a for or while loop.

Upvotes: 5

Related Questions