Count the number of files in a folder (Directory) using VBA Excel-

Count number of files vba excel


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

'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


Count number of files vba excel

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.


Count number of files vba excel



  1. We can also select the folder by dialog box.
  2. We can also find the count of a specific type of a file

Post you may like

Convert multiple excel files into csv files in VBA Excel