List number of files in a folder with the file names in Excel (VBA)

Bbcalagu
Posted by Bbcalagu under Others category on | Points: 40 | Views : 1794
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

Comments or Responses

Login to post response