Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Count Files in Folders

Below you find a example how to count al files or only certain file types in a folder.

Note: Only working in Excel 2011

Copy the Macro and function below in a normal module of your workbook.
The example below will count all the xls files in your Documents folder.

You see that the function call in the macro have 3 arguments
1: Folder Path
2: Filter type 1-5, see other examples below the code
3: Extension string


Tip: Below the code you find other examples that you can use.

Sub TestFileCount()
    Dim FolderPath As String
    Dim Numfiles As Long

    'For testing we let the code pick the Documents folder
    'You can also hard code the path or use code to browse to the folder
    FolderPath = MacScript("return (path to documents folder) as String")

    'Count all files in the folder with the extension xls
    'FileterType argument 1 with as ExtString "xls"
    Numfiles = CountFilesInFolder(FolderPath, 1, "xls")

    MsgBox Numfiles
End Sub


Function CountFilesInFolder(FolderPath As String, FilterType As Long, _
                            ExtString As Variant) As Long
'Ron de Bruin, 31-Oct-2012
    Dim scriptToRun As String
    Dim FilterString As String
    Dim MySplit As Variant
    Dim N As Long

    Select Case FilterType
    Case 1: FilterString = "is in "
        MySplit = Split(ExtString, ",")
        ExtString = "{"""
        For N = LBound(MySplit) To UBound(MySplit)
            If N = UBound(MySplit) Then
                ExtString = ExtString & Trim(MySplit(N)) & """}"
            Else
                ExtString = ExtString & Trim(MySplit(N)) & ""","""
            End If
        Next N

    Case 2: FilterString = "starts with "
    Case 3: FilterString = "ends with "
    Case 4: FilterString = "contains "
    Case 5: FilterString = "is not missing value and name extension is not "
    End Select

    If FilterType = 1 Then
        ExtString = "" & ExtString & ")"
    Else
        ExtString = """" & ExtString & """)"
    End If

    scriptToRun = "tell application ""Finder"" " & vbLf & "try" & vbLf
    scriptToRun = scriptToRun & "set NoOfFiles to count of (files in folder " & Chr(34) & _
       FolderPath & Chr(34) & " whose name extension " & FilterString & ExtString & vbLf
    scriptToRun = scriptToRun & "end try" & vbLf & "end tell"
    On Error Resume Next
    CountFilesInFolder = MacScript(scriptToRun)
    On Error GoTo 0
    
End Function

Test also the examples below :

'Count all files in the folder with extension xls, xlsx and xlsm
'FileterType argument 1 with as ExtString "xls,xlsx,xlsm"
Numfiles = CountFilesInFolder(FolderPath, 1, "xls,xlsm,xlsx")

'Count all files in the folder with extension starts with xl
'FileterType argument 2 with as ExtString "xl"
Numfiles = CountFilesInFolder(FolderPath, 2, "xl")

'Count all files in the folder with extension ends with m
'FileterType argument 3 with as ExtString "m"
Numfiles = CountFilesInFolder(FolderPath, 3, "m")

'Count all files in the folder with extension contains xls
'FileterType argument 4 with as ExtString "xls"
Numfiles = CountFilesInFolder(FolderPath, 4, "xls")

'Count all files in the folder
'FileterType argument 5 with as ExtString ""
Numfiles = CountFilesInFolder(FolderPath, 5, "")