Using Excel VBA to Count Files in Folders and Subfolders

How to Launch the VBA Editor in Excel

Utilizing the Module Tool:

Open your workbook and click Visual Basic in the Developer tab.

Choose Module in Insert.

A module will be created.

Using the View Code Option:

Select a worksheet, choose View Code in the Context Menu.

A VBA window will be displayed.

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

You have a Folder named Exceldemy with multiple types of files: .xls, .txt.

You have a Subfolder with two .xls files.

Example 1 – Counting All Files in Folder and Subfolders

Steps:

Open a new module, enter the following code and click Save.

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()

Starts a sub-procedure.

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

sets the path of the folder that will be counted.

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

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

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

End Sub

Ends the macro.

Function CountFiles(Folder_Path As String) As Integer

CountFiles is called to take a folder path as an argument and return the count of files.

Set Folder_Access = CreateObject("Scripting.FileSystemObject")

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

Set folder = Folder_Access.GetFolder(Folder_Path)

sets the value of the folder variable to the folder object

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

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

goes through each subfolder in the current folder and 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

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

End Function

ends the definition of the CountFiles.

Go back to the worksheet and create a button in the Developer tab.

Choose the button.

In Assign Macro, select the saved macro and click OK.

A MsgBox will display the total count of files in the folder and subfolder.

Example 2 – Counting Specific Types of Files

To count only the number of Excel files (.xls extension) in a folder and its subfolders, use the below code:

Enter the code and run it by pressing F5.

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()

starts the definition of a Sub Procedure named Counting_Specific_Type_Files.

File_Type = "*.xls*"

sets the value of the File_Type variable to .xls: the code will count all files with an xls extension (.xls, .xlsx, .xlsm, etc.).

Folder_Path = InputBox("Provide folder path: ")

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

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

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)

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

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

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

Example 3 – Counting Files from Folders Only

Steps:

Create a new module, use the code, and click 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

Provide the folder path in the MsgBox and click OK.

A final MsgBox will display the count.

Example 4 – Counting Files Containing a Specific String

Steps:

Open a module, enter the macro code and click 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

Enter the folder path into the input box and click OK.

A confirmation MsgBox will display the count of files with the specific word “Final”.

Things to Remember

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 by providing the path to that subfolder.

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

Download the practice workbook.

VBA to Count Files in Folder and Subfolders.xlsm

Get FREE Advanced Excel Exercises with Solutions!

Using Excel VBA to Count Files in Folders and Subfolders – 4 Examples

How to Launch the VBA Editor in Excel

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

Example 1 – Counting All Files in Folder and Subfolders

Example 2 – Counting Specific Types of Files

Example 3 – Counting Files from Folders Only

Example 4 – Counting Files Containing a Specific String

Things to Remember

Frequently Asked Questions

Related Articles

Leave a reply Cancel reply