Excel counting files in a folder with sub-folders wrong

Question

It works right with single folders but if the treee level is higher than 2, the error increases with the length of he tree.

I am trying to get quantity of xl in a folde including subfolders in order to create an array.

(I will use open all files within another sub and create a report)

Any ideas?

Private Sub Countfiles(FF As Scripting.Folder)
        Dim F As Scripting.file
        Dim SubF As Scripting.Folder
        Dim k As Integer

        For Each F In FF.Files
                If F.Path Like "*.xl*" Then
                        k = k + 1
                        Debug.Print r_tot + k
                Else
                End If
        Next

        For Each SubF In FF.Subfolders
                r_tot = r_tot + k
                Countfiles SubF
        Next SubF

End Sub

Instead of using recursive function, just loop through the folder/sub-folders and count files like it's shown in http://stackoverflow.com/questions/16753701/count-files-in-specific-folder-and-display-the-number-into-1-cel Rgds, — Alexander Bell, Jun 29 '14 at 18:35
@alexbell how will that work given the question clearly wants to go furthr than one level of subfolders? — brettdj, Jul 10 '14 at 12:00

score 0 · Answer 1 · answered Jun 29 '14 at 19:44

Use the following code snippet to get files count (of Excel types .xls) in a single folder. Apply it to all folders of interest and add the count to accumulator var.

Function FilesCount(FolderPath)
    Dim FolderPath As String, path As String, count As Integer

    path = FolderPath & "\*.xls"
    Filename = Dir(path)

    Do While Filename <> ""
       count = count + 1
       Filename = Dir()
    Loop

    FilesCount=count
End Sub

Hope this will help. Rgds,

score 0 · Answer 2 · edited Jul 06 '14 at 23:39

Each iteration for the loop has it's own instance of k, you are not returning it's value to the calling routine

You can add the count as a parameter to the Sub

Private Sub Countfiles(FF As Scripting.Folder, ByRef count As Long)
    Dim f As Scripting.file
    Dim SubF As Scripting.Folder

    For Each f In FF.Files
        If f.Path Like "*.xl*" Then
            count = count + 1
        End If
    Next

    For Each SubF In FF.Subfolders
        Countfiles SubF, count
    Next SubF   

End Sub

or convert it to a Function

Function fCountfiles(FF As Scripting.Folder) As Long
    Dim count As Long       
    Dim f As Scripting.file
    Dim SubF As Scripting.Folder

    For Each f In FF.Files
        If f.Path Like "*.xl*" Then
            count = count + 1
        End If
    Next

    For Each SubF In FF.Subfolders
        count = count + fCountfiles(SubF)
    Next SubF
    fCountfiles = count
End Function

brettdj · Answer 3 · 2014-07-06T23:38:24.367

Another approach would be to use PowerShell via VBA - a single line is enough to count files and folders recursively (or to dump a complete list of the same).

The code below is a great improvement on recursive looping using FSO or DIR, but frustratingly long compared to the simplicity of using PowerShell directly which to look from a folder C:\temp is just:

Write-Host ( Get-ChildItem c:\temp -recurse -include *.xls* | Measure-Object | export-csv C:\temp\filename.csv

change strFolder to set the folder of interest for the count of files and folders

code

Sub BYpass()
Dim strFolder As String
Dim StrIn As String
Dim WB As Workbook
Dim X1
Dim X2
strFolder = "c:\temp"
StrIn = "C:\temp\filename.csv"
'export file count
X1 = Shell("powershell.exe Write-Host ( Get-ChildItem " & strFolder & " -recurse -include *.xls* | Measure-Object | export-csv " & StrIn & ")", 1)
'export detail of all files
X2 = Shell("powershell.exe Write-Host ( Get-ChildItem " & strFolder & " -recurse -include *.xls* | export-csv " & StrIn & ")", 1)
Set WB = Workbooks.Open(StrIn)
End Sub

Excel counting files in a folder with sub-folders wrong

3 Answers3