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

Count number of files vba excel

Objective

To count the number of files in a folderĀ  using VBA Excel.

Approach

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.

Code

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

 

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

 

Notes

  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