1

I have the following script. Want the number of folder, subfolders and files:

Sub CountFiles(ByVal path1 As String)

Dim fso As Object
Dim subfolder As Object
Dim file As Object
Dim folder As Object
Dim stetje As Long

Set fso = CreateObject("Scripting.FileSystemObject")

Set folder = fso.GetFolder(path1)

For Each subfolder In folder.SubFolders
 CountFiles (subfolder.path)
 
Next subfolder

For Each file In folder.Files


Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = file.path



Next file


Set fso = Nothing
Set folder = Nothing
Set subfolder = Nothing
Set file = Nothing

End Sub

Which you call as:

Sub someStuff()
Call CountFiles ("c:/temp/test/")
End Sub

This script writes into Excel cells paths to all the folders, subfolders and files

But what I really want is to count the TOTAL of all the the occurrences into a variable.

So instead of this:

 For Each file In folder.Files
    
    
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = file.path
    
    
 Next file

I would like something like this:

 For Each file In folder.Files
    
    number = number +  file.path.Count // of course this line is completely pseudo

 Next file

So the wanted output is for example the number: 2345 and not 2345 rows with paths written out.

Any help / hints would be appreciated!

user1509923
  • 193
  • 1
  • 3
  • 13
  • `folder.Files.Count` is the number of files in the folder – Tim Williams Aug 06 '21 at 22:09
  • Thank you for the reply, but I only get an output like this: 6, 3, 3, 6, 6, 3 which means it returns the number of files for each path... and I'd like to have a TOTAL of All the folders and all the files. This is just the final files for each loop. So even if i sum the together I only get the number of files in the end of each path. so instead – user1509923 Aug 06 '21 at 22:37

2 Answers2

1

Here's a way to do it:

Function CountFiles(ByVal path As String) As Long

    Dim fso As Object
    Dim folder As Object
    Dim subfolder As Object
    Dim amount As Long
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Set folder = fso.GetFolder(path)
    For Each subfolder In folder.SubFolders
        amount = amount + CountFiles(subfolder.path)
    Next subfolder
    
    amount = amount + folder.Files.Count
    
    Set fso = Nothing
    Set folder = Nothing
    Set subfolder = Nothing
    
    CountFiles = amount

End Function

Sub someStuff()
    MsgBox CountFiles("c:/temp/test/")
End Sub

I've turned the sub into a function, which returns the amount of files found in that folder and the subfolders. As before, this works recursively.

Robson
  • 2,008
  • 2
  • 7
  • 26
1

A non-recursive option:

Function FilesCount(fldr As String)
    Dim colFolders As New Collection, fso, num As Long, f As Object, sf As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    colFolders.Add fso.getfolder(fldr) 'add the starting folder
    
    num = 0
    Do While colFolders.Count > 0      'while we still have folders to process...
        Set f = colFolders(1)          '   get the first folder from the collection
        colFolders.Remove 1            '   and remove it from the collection
        num = num + f.Files.Count      '   Add # of files in that folder
        For Each sf In f.subfolders    '     and add each subfolder into the collection
            colFolders.Add sf
        Next sf
    Loop
    FilesCount = num
End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125