Welcome back Excellers to another #MacroMonday blog post in my #Excel 2020 series. Today let’s write an Excel VBA macro to count files in a folder. I have used this macro regularly when I am expecting staff to drop their files into my folder. So, I know I am expecting four files. I do not even have to open any folders to check. Just by this small VBA macro and I know how many files are in my folder. How easy is that?. Want to learn how to do that?. Well, let’s get started. 
Preparing To Write The Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Hit ALT +F11 or
- Select the Developer Tab | Code Group | Visual Basic.
Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
Learn More About Your Personal Macro Workbook (PMW).
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Preparing To Write The Macro.
We need to start the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply CountMyFiles. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines. [stextbox id=’info’] Sub CountMyFiles End Sub [/stextbox]
Declaring Variables.
We need to declare some variables This ensures that Excel creates a memory container for these values. The variables we need to declare for this macro are as follows. [stextbox id=’info’] Dim Folder As String, Path As String, Count As Integer End Sub [/stextbox] These are declaring the type of values expected by Excel. So, the Folder is a string as well as the File Path and the count of files is an integer.
Setting The Folder Path, Path and Filename.
Next, we set the folder, path and filename as the variables declared. [stextbox id=’info’] FolderPath = “C:\Users\EXCEL\Desktop\TEST” Path = FolderPath & “\*” Filename = Dir(Path) End Sub [/stextbox]
Loop Through The Files In Our Folder. Count Files.
We now use the Do While Looping method in Excel to loop through all of the files in our specified folder and count them. A Do…While loop is used when we want to repeat a set of statements as long as the condition is true. The condition may be checked at the beginning of the loop or at the end of the loop. In this case, we are testing the condition if the filename is NOT blank then count the number of files. The count is incremental until all of the files have been looped. [stextbox id=’info’] Do While Filename <> “” Count = Count + 1 Filename = Dir() Loop [/stextbox]
Displaying The Count Of Files In A Message Box.
I have chosen to display the result of the file count in a message box. I find this a great way to display the results quickly. So, I have joined the Count of files to also display the text “files found in folder”. [stextbox id=’info’] MsgBox Count & ” : files found in folder” [/stextbox] 
Ending The Excel Macro.
The code ends once all looping of cells has been completed with the “End Sub" piece of code. This was already entered into the module for us when started the type the name of the macro.
[stextbox id=’info’] End Sub [/stextbox] 
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Macro Mondays Blog Posts.
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.