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
There’s a lot to unpack here. To save time and become really good at VBA, take a look at our VBA Reference Guides. They have over 180 tips and 135 pre-built macros covering the 100 most important topics in VBA.
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.
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I developed this unique 3-part free Excel training program to help you quickly learn VBA in a natural setting: right inside Excel. I'm also going to give you my entire personal macro library for free.