VBA Count Files in Folder and Subfolders
VBA Count Number of Files in Folder and Subfolders. We are checking main folder and all the other available subfolders to count number of files. We use recursive method to check each and every subfolder. In the following tutorial we have explained step by step instructions to run example macro.
VBA Count Number of Files in Folder and Subfolders
Let us see an example macro VBA code to count number of files in folder and Subfolders. In the below example we are specifying root folder path as ‘C:\VBAF1’. You can change this path according to your requirement.
Main Procedure:
'Module level variable Dim iFilesCount As Integer 'VBA Procedure to Count Files in Folder and Subfolders Sub VBAF1_Count_Files_in_Folder_and_Subfolders() 'Variable declaration Dim sFldPath As String 'Define Root Folder Path sFldPath = "C:\VBAF1" 'Çall SubProcedure Call VBAF1_SubProcedure_To_Count_Files_in_Folder_and_Subfolders(sFldPath) MsgBox "Number of files in Folder and Subfolders : " & iFilesCount, vbInformation, "VBAF1" End Sub
Note: We have declared module level variable ‘iFilesCount’. It helps in recursive process. The variable declared at the beginning of the module. Read more details about Module level variable.
SubProcedure(Recursive Method): In the below SubProcedure we are checking all available subfolders using recursive method. We are checking files count and subfolders count in a folder. We use VBA ‘FolderExists’ method of FileSystemObject object(FSO) to check whether specified folder is available or not.
'VBA SubProcedure(Recursive Method) to Count Files in Folder and Subfolders Sub VBAF1_SubProcedure_To_Count_Files_in_Folder_and_Subfolders(sFolderPath As String) 'Variable declaration Dim sFileName As String, oFile As Object, oFolder As Folder Dim iFoldersCount As Integer, iFilesCnt As Integer Dim iAddFilesCount As Integer 'Check for slash If Right(sFolderPath, 1) <> "\" Then sFolderPath = sFolderPath & "\" 'Create FSO Object Set oFSO = CreateObject("Scripting.FileSystemObject") Set oFolder = oFSO.GetFolder(sFolderPath) 'Check Specified Folder exists or not If oFSO.FolderExists(sFolderPath) Then iFilesCnt = oFolder.Files.Count iFilesCount = iFilesCount + iFilesCnt End If 'Loop through all subfolders For Each oSubFolder In oFolder.SubFolders 'Çheck available files count in a folder iAddFilesCount = oSubFolder.Files.Count 'Çheck available subfolders count in a folder iFoldersCount = oSubFolder.SubFolders.Count 'Recursive Method 'Check if any other subfolders within subfolders If iFoldersCount <> 0 Then Call VBAF1_SubProcedure_To_Count_Files_in_Folder_and_Subfolders(sFolderPath & oSubFolder.Name) Else 'Add files count iFilesCount = iFilesCount + iAddFilesCount End If Next End Sub
Output: You can find following output screenshot for your reference. It displays total number of files from a folder and Subfolders.
Instructions to use VBA Macro Code
Here are the instructions to use above macro in Visual basic editor.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
- Now you can find number of files count in a specified folder and folders on the screen.
- you can find output screenshot after VBA Macro code.
Related Articles
You can also learn complete details like syntax, example and etc by clicking on the following buttons.
Hello,
I am trying to understand working of above code.
I can see, you have defined variable type of ifilescount, osubfulder, ifoldercount
but not understood how it is working without setting them further?… what is role of ifilescount while checking of existence of folderpath?
as you can understand I am beginner in VBA It will help me for better understanding.
Thanks in advance.
Hi Swati,
Please find my below explanation for your query.
Code: The below code first checks whether specified folder(sFolderPath) exists or not. If folder exists it enters inside if condition. Where ‘iFilesCnt ‘ is a variable, it consists or contains number of files in a specified folder.
And ‘iFilesCount’ is a nother variable, it accemulates number of files count in each folder.
‘Check Specified Folder exists or not
If oFSO.FolderExists(sFolderPath) Then
iFilesCnt = oFolder.Files.Count
iFilesCount = iFilesCount + iFilesCnt
End If
Hope this helps!
Thanks for visiting our blog.