Count files in a folder using VBA Excel – debugvba.com

count files in folder vba excel

Objective

Count files in a folder using VBA Excel.

Approach

Here we have placed 4 sample files in source folder. We can see the sample files in the image shown below. Then we loop through each file using filesystemobject called objFileSystem. GetFolder(SourceFolder) method of filesystemobject returns a folder object. In simple terms it returns a reference to folder which is located at a path which is stored in variable SourceFolder. Files property in GetFolder(SourceFolder).Files expression returns collection of files in SourceFolder. We have declared the file counter variable called “FileCount” and initialized FileCount as 0. We loop though each file in the source folder and increase the counter by 1 in each iteration. Once we loop through all the files, we showcase the output using message box.


Code

Sub CountFiles()

Dim objFileSystem As Object
Dim SourceFolder As String
Dim FileCount As Integer
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
FileCount = 0

'Path of the folder where files are locted
SourceFolder = "C:\Users\username\Desktop\macro\Final\New\1074_Count_Files\MyFolder\"

'Check if source folder exists
If objFileSystem.FolderExists(SourceFolder) = True Then

'Looping through each file in the source folder
For Each MyFile In objFileSystem.GetFolder(SourceFolder).Files
FileCount = FileCount + 1
Next MyFile

Else
MsgBox "Source folder does not exist"
End If

MsgBox " Number of files in source folder  = " & FileCount
Set objFileSystem = Nothing
     
End Sub

 

We had 4 sample files in source folder.We shall count these files using code above.

count files in folder vba excel

For demonstration purpose we have displayed the message box, which will showcase the count of files.

 

count files in folder vba excel

 


No
tes

  1. We can also count the files using dir function of vba.
  2. We can also customize this code to count any specific (e.g. .xlsx ,*.pptx) type of file as well


Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/files-property
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/getfolder-method

Post you may like

Insert multiple rows and column by VBA Excel