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:
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
Note: the above macro will work for .xlsx & .xls extension & not for .xlsm Macro enabled extension.
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 firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.