VBA Count Files in Folder and Subfolders

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 sub-folders to count number of files. We use recursive method to check each and every sub folder. In the following tutorial we have explain step by step instructions to run example macro.

VBA Count Number of Files in Folder and Sub-folders

Let us see an example macro VBA code to count number of files in folder and Sub-folders. 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.

Module level Variables in VBA

SubProcedure(Recursive Method): In the below Sub-procedure we are checking all available subfolders using recursive method. We are checking files count and sub-folders 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 Sub-folders. Now you can find number of files count in a specified folder and folders on the screen.
VBA Count Number of Files in Folder and Subfolders

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

2 thoughts on “VBA Count Files in Folder and Subfolders”

  1. 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.

  2. 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.

Leave a Comment