Count files in a folder using VBA Excel.
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.
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.
For demonstration purpose we have displayed the message box, which will showcase the count of files.
- We can also count the files using dir function of vba.
- We can also customize this code to count any specific (e.g. .xlsx ,*.pptx) type of file as well
Post you may like