To count the number of files in a folder using VBA Excel.
In this VBA Excel automation, we are looping through each file in the input folder by Dir function.We are using a variable to act as a counter.Total number of files is displayed in cell A1 and it is also displayed on message box.
Option Explicit Sub NumberOfFiles() Dim InputFiles As Variant Dim SourceDataFolder As String Dim NumberOfFiles As Integer SourceDataFolder = "C:\Users\yourusername\Desktop\Input_Data\" 'Initially set the count of files to be zero NumberOfFiles = 0 'Loop through each csv file in source folder InputFiles = Dir(SourceDataFolder & "\*.*") While InputFiles <> "" NumberOfFiles = NumberOfFiles + 1 InputFiles = Dir Wend 'Saving the value of count in cell A1 and showing that in message box Range("A1").Value = NumberOfFiles MsgBox "Number of files in folder - " & NumberOfFiles End Sub
We have used a folder, for input files (input_data). All the files are placed in this folder
When we execute the VBA code, count of files will be saved in cell A1 as shown in the image, we shall also display a message box which will show us the count of file as well.
- We can also select the folder by dialog box.
- We can also find the count of a specific type of a file
Post you may like