To list number of files in a folder with the file names in Excel
Dim fso As New FileSystemObject
Sub CountFiles(ByVal strDir As String)
Dim objFiles As Object
Dim objFolders As Object
Dim obj As Object
Dim lngFileCount As Long
Set objFiles = fso.GetFolder(strDir).Files
lngFileCount = objFiles.Count
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = strDir
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = lngFileCount
For Each obj In objFiles
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = "File"
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = obj.Name
Next obj
Set objFolders = fso.GetFolder(strDir).SubFolders
For Each obj In objFolders
CountFiles (obj.Path)
Next obj
Set objFiles = Nothing
Set objFolders = Nothing
Set obj = Nothing
End Sub