• Count Files (with a specific extension or not) in a folder and subfolders

    This macro can be used to count how many files exist in a folder and subfolders. In addition, if passed the file extension, it can also be used to count only files of a specific type. (For example "xls" files.) If you are looking for a macro that counts files within a specific folder only (ignoring subfolders), please see this entry.

    Macro Purpose:
    • Count all files in a folder and subfolder
    • Count all files of a specific type in a folder and subfolders

    Examples of where this function shines:
    • When checking if you should process a folder for any reason, this can be used to quickly test if any files worth looking at exist inside.

    Macro Weakness(es):
    • None known.

    Versions Tested:
    This function has been tested with Excel 2007. Due to the use of the InStrRev function, it will not work in Excel 97. While I have not tested it with Excel 2000 and Excel 2002 (XP) without any modifications, I don't expect any issues with it. (If you find one, please leave a comment at the bottom of the article.)

    VBA Code Required:
    • Place the following code in a standard module of the workbook you wish to use it in.

    Private Function CountFiles_FolderAndSubFolders(strFolder As String, Optional strExt As String = "*.*") As Double
    'Author          : Ken Puls (www.excelguru.ca)
    'Function purpose: To count files in a folder and all subfolders.  If a file extension is provided,
    '   then count only files of that type, otherwise return a count of all files.
        Dim objFso As Object
        Dim objFiles As Object
        Dim objSubFolder As Object
        Dim objSubFolders As Object
        Dim objFile As Object
        'Set Error Handling
        On Error GoTo EarlyExit
        'Create objects to get a count of files in the directory
        Set objFso = CreateObject("Scripting.FileSystemObject")
        Set objFiles = objFso.getfolder(strFolder).Files
        Set objSubFolders = objFso.getfolder(strFolder).subFolders
        'Count files (that match the extension if provided)
        If strExt = "*.*" Then
            CountFiles_FolderAndSubFolders = objFiles.Count
            For Each objFile In objFiles
                If UCase(Right(objFile.Path, (Len(objFile.Path) - InStrRev(objFile.Path, ".")))) = UCase(strExt) Then
                    CountFiles_FolderAndSubFolders = CountFiles_FolderAndSubFolders + 1
                End If
            Next objFile
        End If
        'Request count of files in subfolders
        For Each objSubFolder In objSubFolders
            CountFiles_FolderAndSubFolders = CountFiles_FolderAndSubFolders + _
            CountFiles_FolderAndSubFolders(objSubFolder.Path, strExt)
        Next objSubFolder
        'Clean up
        On Error Resume Next
        Set objFile = Nothing
        Set objFiles = Nothing
        Set objFso = Nothing
        On Error GoTo 0
    End Function
    How to use the code:
    • Call it from another routine, as shown using a FileDialog example below. (FileDialogFolderPicker may not work in Excel 2000.)
    • Please note that you should provide the full file path to the folder as the argument.
    • You may supply or ignore the trailing \ on a directory if you choose. It will work either way.

    Sub Test()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test the CountFiles function
        Dim flDlg As FileDialog
        Dim dblCount As Double
        Set flDlg = Application.FileDialog(msoFileDialogFolderPicker)
        dblCount = CountFiles_FolderAndSubFolders(flDlg.SelectedItems(1))
        Debug.Print dblCount
    End Sub


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 2 Comments
    1. Pvelasco's Avatar
      Pvelasco -
      Excelente código!
      Muchas gracias desde Perú.

      Code excelent!
      Thanks very much from Peru.
    1. Skozy's Avatar
      Skozy -
      Hello Ken,

      I'm somewhat of a Noob to this stuff so thanks for your patience. I implemented this code into one of my sheets. After I added it I put in one of the cells for example - =CountFiles_FolderAndSubFolders("W:\Yoyo\jenga\","*.*") It returns the total files in the folders and subfolders. When I change the last extension to "*.pdf" it comes back with 0 when I know there is 2 pdfs in the folders. Am I writing the function wrong in the cell?