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

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

Code:
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
    Else
        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

EarlyExit:
    '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.

Code:
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)
    flDlg.Show
    dblCount = CountFiles_FolderAndSubFolders(flDlg.SelectedItems(1))
    Debug.Print dblCount
End Sub

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts

Excel Fundamentals Boot Camp

COACHED TRAINING: Excel Fundamentals Boot Camp Course Description In the Fundamentals Boot Camp, you will begin with a review core skills for the Excel analyst. This section is geared to

Read More »