Use this VBA macro to count the files in a folder. This macro can return the number of total files in a folder or the number of files of a certain type. Because it’s based on my VBA loop through files in a folder macro, it accepts the same two arguments, one of which is optional:
- The directory you want to count the number of files in
- An optional filter for things like a file extension. This argument accepts an * as a wild card!
This macro works really fast and by default reports the total number of files in a msgbox. You can change it to send the total number of files to a cell if you desire.
Macro to Count Files in a Folder
Private Sub CountFilesInFolder(strDir As String, Optional strType As String) 'DEVELOPER: Ryan Wells (wellsr.com) 'DESCRIPTION: This macro counts the files in a folder and retuns the result in a msgbox 'INPUT: Pass the procedure a string with your directory path and an optional ' file extension with the * wildcard 'EXAMPLES: Call CountFilesInFolder("C:\Users\Ryan\") ' Call CountFilesInFolder("C:\Users\Ryan\", "*txt") Dim file As Variant, i As Integer If Right(strDir, 1) <> "\" Then strDir = strDir & "\" file = Dir(strDir & strType) While (file <> "") i = i + 1 file = Dir Wend MsgBox i End Sub
Make powerful macros with our free VBA Developer's Kit
There's a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier.
How to use the CountFilesInFolder Macro
To help get you started, here are a few examples of how to use the CountFilesInFolder macro:
Count txt files in Folder
Sub Demo() Call CountFilesInFolder("C:\Users\Ryan\Documents\", "*txt") End Sub
Count Excel files in Folder
Sub Demo2() Call CountFilesInFolder("C:\Users\Ryan\Documents\", "*.xls*") End Sub
Count All Files in Folder
Sub Demo3() Call CountFilesInFolder("C:\Users\Ryan\Documents\") End Sub
Only Count Files with "report" in the Filename
Sub Demo4() Call CountFilesInFolder("C:\Users\Ryan\Documents\", "*report*") End Sub
I think this macro is a great way to start off the month of August! I hope you you agree:)
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.