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

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:

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

Hence, choose Module from the Insert tab.

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

Using View Code Option:

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

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.

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

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

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:

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.

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

After creating the button, choose the button.

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

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

2. Counting Specific Types 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.

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.

3. 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:

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

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

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

4. 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:

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

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

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

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!

VBA to Count Files in Folder and Subfolders.xlsm

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.

Get FREE Advanced Excel Exercises with Solutions!

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

How to Launch VBA Editor in Excel

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

1. Counting All Files in Folder and Subfolders

2. Counting Specific Types of Files

3. Counting Files from Folders Only

4. Counting Files Containing Specific String

Things to Remember

Frequently Asked Questions

Conclusion

Related Articles

Leave a reply Cancel reply