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
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.
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!
About The VBA Tutorials Blog
The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.Follow