How To Count Worksheets In Multiple Files in Excel VBA?
Tags: tocountworksheetsinm, vbamacros
Published: 2021-09-18 05:36:44

---
title: How To Count Worksheets In Multiple Files in Excel VBA?
published: true
date: 2021-09-17 02:09:44 UTC
tags: ToCountWorksheetsInM,VBAMacros
canonical_url: https://geekexcel.com/how-to-count-worksheets-in-multiple-files-in-excel-vba/
---

In this tutorial, we will guide you on how to **count worksheets in multiple files** in Excel VBA. Let’s get them below!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## Example

- Firstly, you need to **create a sample data** in the Excel Spreadsheet.

![](https://geekexcel.com/wp-content/uploads/2021/09/sample-data-15.png)<figcaption>Sample data</figcaption>

- In the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to  **copy and paste the code**  given below.

```
Sub ListSheetCounts()
    Dim Cell As Range
    Dim Conn As Object
    Dim Cat As Object
    Dim ConnStr As String
    Dim n As Long
    Dim Rng As Range
    Dim RngEnd As Range
    Dim WkbPath As Variant
    Dim Wks As Worksheet
    
      ' Folder path where the workbooks are located.
        WkbPath = "C:\Users\Test"
        
      ' Name of worksheet with the workbook list.
        Set Wks = Worksheets("Sheet1")
        
      ' Starting cell of workbook list.
        Set Rng = Wks.Range("A2")
        
      ' Get the range of cells in the workbook name list.
        Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
        If RngEnd.Row >= Rng.Row Then Set Rng = Wks.Range(Rng, RngEnd)
        
      ' Create the needed ADO objects fro this macro.
        Set Conn = CreateObject("ADODB.Connection")
        Set Cat = CreateObject("ADOX.Catalog")
        
      ' Add a final backslash the path if needed.
        WkbPath = IIf(Right(WkbPath, 1) <> "\", WkbPath & "\", WkbPath)
        
      ' Step through each cell in the workbook list.
        For Each Cell In Rng
                
          ' Get the worksheet count for the workbook.
            ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
                    & WkbPath & Cell _
                    & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"""
            Conn.Open ConnStr
                
            Set Cat.ActiveConnection = Conn
        
          ' Copy the count to the cell one column to right of the workbook name in the list.
            Cell.Offset(n, 1) = Cat.Tables.Count
                
            Conn.Close
            
        Next Cell
        
      ' Clean up.
        Set Cat = Nothing
        Set Conn = Nothing
End Sub

```

- You need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-Code-44-1024x868.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Now, you have to make sure that your  **macro name is selected**  and click the  **Run ** button.

![](https://geekexcel.com/wp-content/uploads/2021/09/Run-the-Code-25.png)<figcaption>Run the Code</figcaption>

- Finally, you will receive the  **output ** in the **Microsoft Excel**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Output-16.png)<figcaption>Output</figcaption>

**NOTE:** The above **macro** will work for **.xlsx & .xls extension** & not for **.xlsm Macro** enabled extension.

- Firstly, the above **files are .xlsx extension**.
- Then, you have to **add dummy Excel sheet** i.e. Sheet 10.
- Now, in case you have a **file with same name having .xlsx & .xls extensions** , then you need to mention the **name of the file** with their respective extensions as well in our test file (column A) so that macro can identify the file we are referring to & give us the correct result.
- If we have not mentioned or have missed mentioning the **extension for the file with same name** , then the **macro** will give us the **count of .xlsx extension** and refer to the **output** given below.

![](https://geekexcel.com/wp-content/uploads/2021/09/output1-2.png)<figcaption>count of .xlsx extension</figcaption>

- To get the **number of sheets for Sheet 10 with .xlsx & .xls extensions** , you need to mention the **file name** with their respective extensions.
- Finally, the **snapshot of the final output** is given below.

![](https://geekexcel.com/wp-content/uploads/2021/09/Final-output-4.png)<figcaption>Final output</figcaption>

## End of the Line

We hope that this tutorial gives you guidelines on how to **count worksheets in multiple files** in Excel VBA. Please leave a comment in case of any  **queries,**  and don’t forget to mention your valuable  **suggestions**  as well. Thank you so much for Visiting Our Site!! Continue learning on **[Geek Excel](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Further Reference:**

- **[Excel Formulas to Remove File Extension from Filename ~ Simple Tricks!!](https://geekexcel.com/excel-formulas-to-remove-file-extension-from-filename/)**
- **[How to Rename Multiple Files using Macros (VBA) in Excel 365?](https://geekexcel.com/how-to-rename-multiple-files-using-macros-vba-in-excel-365/)**
- **[How to List All Files and Sub-Directories of a Folder in Excel 365?](https://geekexcel.com/how-to-list-all-files-and-sub-directories-of-a-folder-in-excel-365/)**
- **[Split/Save Each Worksheet of One Workbook as Separate Excel/Txt/Csv/Pdf Files in Excel 365!!](https://geekexcel.com/split-save-each-worksheet-of-one-workbook-as-separate-excel-txt-csv-pdf-files-in-excel-365/)**
- **[How to Print Multiple Workbooks/Worksheets from Directories in Excel 365?](https://geekexcel.com/how-to-print-multiple-workbooks-worksheets-from-directories-in-excel-365/)**