count worksheets in xlsx file

DerbyDad03 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."

Humar July 29, 2010 at 16:33:52

Hi, 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: Sheet1 Sheet2 and Summary In which case the result is 1 Please clarify Regards

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.

Humar July 31, 2010 at 08:32:51

Hi, 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 ActiveSheet.Cells.Clear '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", _ MultiSelect:=True) '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, _ Notify:=False) '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). _ Columns("A:B").EntireColumn.AutoFit 're-enable warning messages Application.DisplayAlerts = True 're-enable screen updates Application.ScreenUpdating = True Exit Sub 'error handler ErrHnd: Err.Clear '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. Regards