The following VBA can help you easily count a specific type of files in a certain folder, such as count all .xlsx files in a folder. Please do as follows:

1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and then paste the below VBA code into the opening Module window.

VBA: Count a specific file in a certain folder

Sub CountFiles()
Dim xFolder As String
Dim xPath As String
Dim xCount As Long
Dim xFiDialog As FileDialog
Dim xFile As String
Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
If xFiDialog.Show = -1 Then
xFolder = xFiDialog.SelectedItems(1)
End If
If xFolder = "" Then Exit Sub
xPath = xFolder & "\*.xlsx"
xFile = Dir(xPath)
Do While xFile <> ""
xCount = xCount + 1
xFile = Dir()
Loop
MsgBox xCount & " files found"
End Sub

Note: The above VBA code will count the .xlsx files in a certain folder. For counting other type of files, please find out xPath = xFolder & "\*.xlsx", and change the .xlsx to other file extension based on your needs.

3. Press the F5 key or click the Run button to run this VBA code.

4. In the opening Browse dialog box, select the certain folder that you will count the specific type of files in, and click the OK button.

5. Now a dialog box will come out and show you how many the specific type of files exist in the certain folder. Just close the dialog box.

Easily combine multiple worksheets/workbooks/CSV files into one worksheet/workbook

It may be tedious to combine dozens of sheets from different workbooks into one sheet. But with Kutools for Excel’s Combine (worksheets and workbooks) utility, you can get it done with just several clicks!

If you have opened a folder, normally you will get the number of all files at the bottom of this folder. This method will introduce Kutools for Excel’s Open Containing Folder utility to open the folder which contains current workbook, and then get the number of files in this folder.

1. Click Kutools Plus > Workbook > Open Containing Folder. See screenshot:

2. Now the folder containing current workbook is opening.

Unselect any file in the opening folder, and you will see the number of all files at the bottom of this folder as below screenshot shown:

Note: The Open Containing Folder utility will be invalid if you have never saved current workbook before.

Sometimes, you may want to count several types of files in a folder and its subfolders. Actually Kutools for Excel provides another Filename List utility to solve it with ease.

1. Click Kutools Plus > Import / Export > Filename List to open the Filename List dialog box.

2. In the opening Filename List dialog box, please do as follows:

3. Click the Ok button in the Filename List dialog box.

4. Now all files of specific types are listing in a new worksheet. Just select the first column without the column header, and you will get the number of files in the Status Bar as below screenshot shown: