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
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:)
Submit a comment below if you like this macro or have more questions. As always, subscribe to my email list, share this article on social media and follow me on Google+ and Twitter for more great VBA content!
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow