count worksheets in xlsx file

July 29, 2010 at 11:01:57
Specs: Windows XP
I need code to produce a list of files in a folder and the number of worsheets in each file. I need a count of all the files without "sheet" in the worksheet name.

See More: count worksheets in xlsx file

Report •

July 29, 2010 at 13:34:45
You've asked for a number things:

1 - A list of files in a folder
2 - The number of wor(k)sheets in each file.
3 - A count of all the files without "sheet" in the worksheet name.

1 & 2 are pretty straight forward, although I believe the code is going to have to open the files before it can count the sheets.

# 3 is a bit confusing. Are you looking for a single number, like "6"?

e.g "6 files do not have a sheet with Sheet in the name of any worksheet."

Report •

July 29, 2010 at 16:33:52

When you say I need a count of all the files without "sheet" in the worksheet name
Do you mean that you need a count of the number of worksheets in a workbook that do not have "Sheet" in the name.

For example MyXLFile.xls has three worksheets named:
Sheet2 and
In which case the result is 1

Please clarify


Report •

July 30, 2010 at 08:29:24
Within each file, I need a count of the worksheets. There are always two extra sheets in the file because that is how Excel is set up. I only want to count the sheets that I inserted. I figured the best way to do that would be to exclude the sheets with "sheet" in the worsheet name. I am open to other methods. Thanks for helping.

Report •

Related Solutions

July 31, 2010 at 08:32:51

Here is a macro that will list the names of workbooks with the number of worksheets in each workbook that do not start with "Sheet".

The selection of files to test is made using the standard Open dialog box.
Only xls and xlsm files are shown, but this can be changed in the code - in this parameter:

FileFilter:="Excel workbooks (*.xls; *.xlsm),*.xls;*.xlsm",

Each xls and xlsm file is opened and the worksheet names are checked.
To stop the screen constantly changing as workbooks are opened and closed, screen updating is turned off, but you will see Workbook names appear and disappear in the taskbar.

Each time this is run, the list of names and sheet counts are erased, and a new list is created. This could be changed if necessary.

I suggest creating a new workbook, and saving it as say "Worksheet counter.xls"
Then add a button to Sheet1

As you haven't said what version of Excel you used, the following instructions are for Excel2003.
On Sheet1 create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Get Sheet Counts' or something else suitable
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim strThisWs As String
Dim strThisWb As String
Dim fsArray As Variant
Dim wb As Workbook
Dim ws As Worksheet
Dim intWsCount As Integer
Dim intRow As Integer
Dim n As Integer

On Error GoTo ErrHnd

'get name of this workbook
strThisWb = ActiveWorkbook.Name
'get name of this worksheet
strThisWs = ActiveSheet.Name

'clear existing data

'add headers
ActiveSheet.Range("A1").Value = "Filename"
ActiveSheet.Range("B1").Value = "Non-'Sheet' count"

'stop warning messages as workbooks are opened
Application.DisplayAlerts = False

'disable screen updates - to stop showing opened workbooks
Application.ScreenUpdating = False

'set row offset counter to 1
intRow = 1

'use the Open dialog box, filtered for xls and xlsm filenames
fsArray = Application.GetOpenFilename( _
FileFilter:="Excel workbooks (*.xls; *.xlsm),*.xls;*.xlsm", _
Title:="Select workbooks to test", _

'filenames are in an array (fsArray)
'iterate through the workbook names
For n = 1 To UBound(fsArray, 1)
    'open workbook
    Set wb = Workbooks.Open(fsArray(n), _
            UpdateLinks:=0, _
            IgnoreReadOnlyRecommended:=True, _
    'set counter to zero
    intWsCount = 0
    'go through each worksheet in workbook
    For Each ws In wb.Worksheets()
        'test if worksheet name does not start with Sheet
        If Left(ws.Name, 5) <> "Sheet" Then
            'count 'non-Sheet' worksheets
            intWsCount = intWsCount + 1
        End If
    Next ws
    'save workbook name and worksheet count
    Workbooks(strThisWb).Worksheets(strThisWs).Range("A1"). _
            Offset(intRow, 0) = wb.Name
    Workbooks(strThisWb).Worksheets(strThisWs).Range("A1"). _
            Offset(intRow, 1) = intWsCount
    'increment row offset counter
    intRow = intRow + 1
    'close each workbook
    wb.Close SaveChanges:=False
Next n

'adjust column widths
Workbooks(strThisWb).Worksheets(strThisWs). _

're-enable warning messages
Application.DisplayAlerts = True

're-enable screen updates
Application.ScreenUpdating = True
Exit Sub

'error handler
're-enable warning messages
Application.DisplayAlerts = True
're-enable screen updates
Application.ScreenUpdating = True
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

As this code opens and closes all the workbooks, I strongly suggest that you make a copy of the folder(s) containing the workbooks and run this on the copy.
The macro opens all of the workbooks. It is set to close each one without saving it, so it should not affect any of the data, but I cannot be sure of that.
This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your workbooks to ensure that it works 'as expected'

Click the 'Get Sheet Counts' button to run the macro.


Report •

Ask Question