Consider a situation, where you are encountering with number of excel files on a daily basis & you want a quick mechanism to help you finding the count of worksheets present in each workbook. If you have a similar problem then you must not miss this article for it will help you a lot.
In this article, we will learn how to count worksheets in multiple files with VBA code.
Question: I need to macro that can read through a list of file names and return the number of worksheets that are present in each of the files (this is an audit mechanism to ensure that the correct number of worksheets are present in a series of files created via another process). The macro would need to establish the path of the folder where the files are located (all in the same folder), then locate the first file, identify the worksheet count, and repeat for the next file etc.
I thought I could do this with a formula by simply referencing the file names but I believe Excel does not have a straight forward formula for worksheet counts. Thanks!
If you want to read the original question then click here
Following is the snapshot of files saved in a folder with .xlsx extension
Note: There are no password protected files.
To get the code, we need to follow the below steps to launch VB editor:
- Click on Developer tab
- From Code group, select Visual Basic
- Copy the below code in the standard module
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
- As we run the macro, we will get the number of worksheets. Refer below snapshot:
Note: the above macro will work for .xlsx & .xls extension & not for .xlsm Macro enabled extension.
- All of the above files are .xlsx extension
- Let us add dummy excel sheet i.e. Sheet 10
- In case we have a file with same name having .xlsx & .xls extensions, then we 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 to mention the extension for the file with same name, then the macro will give us the count of .xlsx extension. Refer below snapshot:
- To get the number of sheets for Sheet 10 with .xlsx & .xls extensions, we need to mention the file name with their respective extensions
The snapshot of the final output is depicted below:
Conclusion: Using above macro code we can count number of worksheets in multiple files & if require to get the custom result we can do little bit of modification in VBA code.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]