VBA excel count total number of folders (and file)

Question

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!

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 — Smeagol2
– Smeagol2, Commented Aug 6, 2021 at 22:37

Robson · Accepted Answer · 2021-08-06 22:13:35Z

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.

answered Aug 6, 2021 at 22:13

Robson

2,0322 gold badges10 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Williams · Accepted Answer · 2021-08-06 22:16:55Z

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

Collectives™ on Stack Overflow

VBA excel count total number of folders (and file)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related