Excel VBA to Count Files in Folder and Subfolders (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Counting the number of files in a folder and its subfolders can be a tedious and time-consuming task, especially if there are a large number of files involved. However, using Excel VBA, you can automate this process and make it much faster and more efficient. Now you can apply Excel VBA to Count Files in Folder and Subfolders and display the results in an Excel worksheet.

In the following you will find an overview of counting files in folder and subfolder using Excel VBA.


How to Launch VBA Editor in Excel

VBA (Visual Basic for Applications) provides a user-friendly interface for writing VBA code. There are several ways of launching a VBA editor in Excel. Below I have shared 2 simple ways of launching a VBA editor.

Utilizing Module Tool:

Visiting the Visual Basic option from Developer tab

  • To start with, open your workbook and click the Visual Basic option from the Developer tab.

Opening a module from the Insert option

  • Hence, choose Module from the Insert tab.

VBA window to write and manage code

  • As a result, a module will be created where you can type and manage your VBA code.

Using View Code Option:

Choosing View Code option from Advanced options

  • Selecting a worksheet, choose the View Code feature from the Context Menu.

VBA window for the chosen worksheet

  • Immediately, a VBA window will be opened for the chosen sheet to apply macro code.

Excel VBA to Count Files in Folder and Subfolders: 4 Suitable Examples

In the following, I have shared 4 simple and easy ways to count files in folder and subfolders using Excel VBA.

Folder containing multiple types of files

Suppose we have a Folder named Exceldemy where we have multiple types of files like .xls, .txt.

Subfolder containing xlsx type files

In addition, we have a Subfolder where we have two .xls files. Now using VBA code we will count total files in folder and subfolders.


1. Counting All Files in Folder and Subfolders

overview of excel vba to count files in folder and subfolders

Counting all the files in a folder and its subfolders is a common task that is often necessary when managing large amounts of data or files. By knowing the total number of files in a folder and its subfolders, you can quickly get an idea of the size and scope of a project, and plan accordingly. To do so with a single click we will use the below trick.

Steps:

excel vba code to count all files in folder and subfolders

  • Open a new module, place the following code and save it by hitting the Save icon.
Sub Count_Files_In_Folder_and_Subfolders()
Folder_Path = "C:\Users\PC 06\Desktop\Exceldemy"
Output_Files = CountFiles("C:\Users\PC 06\Desktop\Exceldemy")
MsgBox "Total files in the folder and it's subfolder is: " & Output_Files
End Sub
Function CountFiles(Folder_Path As String) As Integer
Set Folder_Access = CreateObject("Scripting.FileSystemObject")
Set folder = Folder_Access.GetFolder(Folder_Path)
For Each file In folder.Files
count = count + 1
Next file
For Each subfolder In folder.SubFolders
count = count + CountFiles(subfolder.Path)
Next subfolder
CountFiles = count
End Function

Code Breakdown:

Sub Count_Files_In_Folder_and_Subfolders()

Starting of a sub-procedure of the macro.

Folder_Path = "C:\Users\PC 06\Desktop\Exceldemy"

This sets the path of the folder that will be counted.

Output_Files = CountFiles("C:\Users\PC 06\Desktop\Exceldemy")

This line calls the function CountFiles and passes the folder path as an argument.

MsgBox "Total files in the folder and it's subfolder is: " & Output_Files 

In this part, a message box is called to display the total number of files found in the folder and its subfolders.

End Sub

End of macro.

Function CountFiles(Folder_Path As String) As Integer

Here a function named CountFiles is called that takes a folder path as an argument and returns the count of files.

Set Folder_Access = CreateObject("Scripting.FileSystemObject")

This line creates a new FileSystemObject, which provides access to the file system.

Set folder = Folder_Access.GetFolder(Folder_Path)

Next, this line sets the value of the folder variable to the folder object corresponding to the folder path provided as input to the function.

For Each file In folder.Files
count = count + 1
Next file

This loop iterates through each file in the current folder and increments the count variable by 1 for each file.

For Each subfolder In folder.SubFolders
count = count + CountFiles(subfolder.Path)
Next subfolder

Here, this loop goes through each subfolder in the current folder and recursively calls the CountFiles function with the subfolder path as input. The count of files in the subfolder and its subfolders is added to the count variable.

CountFiles = count

This line sets the return value of the CountFiles function to the final count of files in the folder and its subfolders.

End Function

Finally, this line ends the definition of the CountFiles.

Creating a macro button from the Developer tab

  • Then, coming back to the worksheet we will create a button from the Developer tab.

Selecting Assign macro option to assign code

  • After creating the button, choose the button.

Choosing saved macro code for the created button

  • From the Assign Macro window select your saved macro and press OK.

Final output counting all files in folder and subfolder

  • Finally, a MsgBox will provide the total count of files for the folder and subfolder. It’s that simple.

Read More: Excel VBA to List Files in Folder with Specific Extension 


2. Counting Specific Types of Files

Overview of counting specific type of files

In order to count a specific type of file using VBA can be useful when you need to quickly determine the number of files of a particular type in a folder and its subfolders. For example, if you need to count only the number of Excel files (files with .xls extension) in a folder and its subfolders, you can use the below code to automate this process.

VBA code to count specific type of files

  • Similar to the previous method, write the code and apply it by hitting the F5 key from the keyboard.
Sub Counting_Specific_Type_Files()
File_Type = "*.xls*"
Folder_Path = InputBox("Provide folder path: ")
If Right(Folder_Path, 1) <> "\" Then
Folder_Path = Folder_Path & "\"
Count_Files = Dir(Folder_Path & File_Type)
End If
While (Count_Files <> "")
i = i + 1
Count_Files = Dir
Wend
MsgBox "Total files in the folder is: " & i
End Sub

Code Breakdown:

Sub Counting_Specific_Type_Files()

This line starts the definition of a Sub Procedure named Counting_Specific_Type_Files.

File_Type = "*.xls*"

Next, this line sets the value of the File_Type variable to .xls, which means that the code will count all files with an xls extension (such as .xls, .xlsx, .xlsm, etc.).

Folder_Path = InputBox("Provide folder path: ")

This line displays an input box asking the user to provide a folder path.

If Right(Folder_Path, 1) <> "\" Then
Folder_Path = Folder_Path & "\"

These lines check whether the provided folder path ends with a backslash (“”). If it doesn’t, the code adds a backslash to the end of the folder path.

Count_Files = Dir(Folder_Path & File_Type)

This line sets the value of the Count_Files variable to the first file in the folder that matches the specified file type.

While (Count_Files <> "")
i = i + 1
Count_Files = Dir
Wend

Here, this loop iterates through all files in the folder and its subfolders that match the specified file type. For each matching file, the loop increments the i variable by 1. The Dir function is used to retrieve the name of the next matching file.

MsgBox "Total files in the folder is: " & i

This line displays a message box with the total count of files in the folder that match the specified file type.

Read More: Excel VBA to List Files in Folder and Subfolders 


3. Counting Files from Folders Only

Overview of counting files from folders only

Sometimes you might need to count files from folders only except subfolders. This can be important if you want to quickly get a count of all files at the top level of a folder without including files in subfolders. In this method, I am sharing with you a simple VBA code that will help you to count files from folders only ignoring subfolders.

Steps:VBA code to count file from folders only

  • Simply, create a new module, put the code, and hit Run.
Sub Counting_Files_Folders_Only()
Folder_Path = InputBox("Provide folder path: ")
If Right(Folder_Path, 1) <> "\" Then
Folder_Path = Folder_Path & "\"
Count_Files = Dir(Folder_Path)
End If
While (Count_Files <> "")
i = i + 1
Count_Files = Dir
Wend
MsgBox "Total files in the folder is: " & i
End Sub

MsgBox to insert folder path

  • Thereafter, provide the folder path inside the MsgBox and click OK.

Final output counting files from folder only

  • As a result, a final MsgBox will popup counting files from the folder.

4. Counting Files Containing Specific String

Overview of counting files containing specific string

If you want you can count files in a folder containing specific words or phrases. To do that, you just need to modify the recursive function that counts all files in a folder and its subfolders.

Steps:

VBA code to count files containing specific string

  • Opening a module, insert the macro code and Run.
Sub Counting_Specific_String()
File_Type = "*Final*"
Folder_Path = InputBox("Provide folder path: ")
If Right(Folder_Path, 1) <> "\" Then
Folder_Path = Folder_Path & "\"
Count_Files = Dir(Folder_Path & File_Type)
End If
While (Count_Files <> "")
i = i + 1
Count_Files = Dir
Wend
MsgBox "Total files in the folder containing 'Final' is: " & i
End Sub

Insertion of folder path inside the InputBox field

  • Hence, insert the folder path into the input box field and click OK.

Final result counting files containing a specific string

  • Finally, a confirmation MsgBox will display counting files with the specific word “Final” from the folder. Simple isn’t it?

Read More: How to Use Excel VBA to List Files in Folder


Things to Remember

  • While specifying the folder path when using these codes make sure you input a valid path to the folder that you want to count the files in.
  • At the time of inserting the file type you should include the wildcard character (*) to match all files with the specified extension.

Frequently Asked Questions

  • Can I use these codes to count files in a specific subfolder?

Yes, you can modify the codes to count files in a specific subfolder by providing the path to that subfolder instead of the parent folder.

  • Do these codes also count hidden files?

Yes, these codes count all files in the folder and its subfolders, including hidden files.


Download Practice Workbook

You can download our practice workbook from here for free!


Conclusion

Hope this article gave you enough idea to use the Excel VBA to Count Files in Folder and Subfolders. The article provides two examples of code, one using a recursive function and the other allowing the user to specify a file type. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo