VBA Macro that Counts the Number of Files in a Folder

You may want to count the number of PNG files in a folder. This is pretty easy if you only have one folder. You just have to right-click the folder and then click the “Properties” attribute. However, this can be a pain if you have a lot of folders, because you need to do this process for every one of them.

Fortunately, you can solve this process pretty easily if you have a VBA macro that does this job. We created one that can this. It has a loop, so you can copy and paste a number of folder names in the first column and then it will show the results for every folder in the second column.

VBA Macro to Count Files in Folders

Sub CountFilesInFolders()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim iRow As Integer
Dim folderPath As String
Dim fileCount As Integer

' Create FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Loop through each row in column A until the first empty cell is encountered
iRow = 1
Do Until IsEmpty(Cells(iRow, 1).Value)
    ' Get folder path from column A
    folderPath = Cells(iRow, 1).Value

    ' Check if folder exists
    If objFSO.FolderExists(folderPath) Then
        ' Set objFolder to the folder
        Set objFolder = objFSO.GetFolder(folderPath)

        ' Initialize file count
        fileCount = 0

        ' Loop through each file in the folder and count
        For Each objFile In objFolder.Files
            fileCount = fileCount + 1
        Next objFile

        ' Output file count to column B
        Cells(iRow, 2).Value = fileCount
    Else
        ' If folder doesn't exist, output error message
        Cells(iRow, 2).Value = "Folder doesn't exist"
    End If

    ' Move to the next row
    iRow = iRow + 1
Loop

' Release objects
Set objFile = Nothing
Set objFolder = Nothing
Set objFSO = Nothing

End Sub

How Do You Run this Program?

It is pretty easy to run this macro. You just have to follow these steps:

1. Open Excel and make sure that you have a file that is enabled to handle macros.
2. Press Alt + F11 to open the VBA Editor.
3. Insert a new module by clicking Insert and then click the Module option.
4. Copy and paste the code that we showed above into the module window.
5. Close the VBA Editor.
6. Copy and paste all of your folder paths in the first column (i.e. Column A)
7. Run the macro CountFilesInFolders: Developer > Macros > CountFilesInFolders > Run.

You could also run the macro by clicking the “Run” button in the VBA Editor. It will loop through all of the folder paths in the first column and share the number of files in the second column.