Count TOTAL number of files in folder and subfolders

[Leggs11]

please help me, this is really frustrating me..

I have a spreadsheet where I need a certain cell ($K$4) to show the number of files within a folder (E.g. C:\Temp) and any and all subfolders below it that contain the value of cell $G$4 within the file name... So far I have been searching online for the past week and can't get very far. I found something that looked promising, but it appears to only count the original folder files that contain the text from $G$4. Below is the VBA that I found and tried to modify myself.... PLEASE HELP. and thank you in advance.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim FolderPath As String, path As String, count As Integer
    
    With Target
        If Not Intersect(Range("G4"), .Cells) Is Nothing Then
            Application.EnableEvents = False
                    If IsEmpty(.Value) Then
                        .Offset(0, 1).Value = ""
                    Else
                        FolderPath = "T:\11 - Personal Folders\Ryan Legg\- Service Manager\"
                        path = FolderPath & "*" & Range("G4") & "*"
                        Filename = Dir(path)
                        Do While Filename <> ""
                        count = count + 1
                            Filename = Dir()
                        Loop
                        Range("K4").Value = count
                    End If
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[Leggs11]

perhaps this could be promising? but I wouldn't even know where to begin to alter this one:

Option Explicit

Sub Demo()
    Dim fso As Object 'FileSystemObject
    Dim fldStart As Object 'Folder
    Dim fld As Object 'Folder
    Dim fl As Object 'File
    Dim Mask As String

    Set fso = CreateObject("scripting.FileSystemObject") ' late binding
    'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

    Set fldStart = fso.GetFolder("C:\Your\Start\Folder") ' <-- use your FileDialog code here

    Mask = "*.xls"
    Debug.Print fldStart.Path & "\"
    ListFiles fldStart, Mask
    For Each fld In fldStart.SubFolders
        ListFiles fld, Mask
        ListFolders fld, Mask
    Next
End Sub


Sub ListFolders(fldStart As Object, Mask As String)
    Dim fld As Object 'Folder
    For Each fld In fldStart.SubFolders
        Debug.Print fld.Path & "\"
        ListFiles fld, Mask
        ListFolders fld, Mask
    Next

End Sub

Sub ListFiles(fld As Object, Mask As String)
    Dim fl As Object 'File
    For Each fl In fld.Files
        If fl.Name Like Mask Then
            Debug.Print fld.Path & "\" & fl.Name
        End If
    Next
End Sub

 

[daverunt]

Hi

I don't think the Dir function is capable of recursing so you have to use FSO.

I've used this in the past to list files. I have adapted it to count but you'll need to change it to suit your needs then tidy it up with Dim statements etc.
I would try it as is before you adapt it to a Worksheet event.

Public Count                            'need this to keep the count going after each call on the macro

Sub Count_files()

    Call Step_Through_Folder("C:\junk\")

     Range("K4").Value = Count
     
End Sub

Function Step_Through_Folder(sFolderName As String)

    Set oFSO = New Scripting.FileSystemObject
    Set oFolder = oFSO.GetFolder(sFolderName)
      
        For Each oFileItem In oFolder.Files
            sFilename = oFileItem.Name
                If InStr(LCase(sFilename), LCase(Range("G4").Value)) > 0 Then
                    Count = Count + 1
                End If
            
        Next oFileItem
 
    For Each oSubFolder In oFolder.SubFolders
        Call Step_Through_Folder(oSubFolder.Path)
    Next oSubFolder
    
  

End Function

 

[Leggs11]

Thanks daverunt, im pretty new to VBA and pretty much useless with it, I can alter things in VBA that seem to me to be plain common sense. But that code there just seems to go right over my head. lol
Where in VBA would you paste this code? a Module or in the sheets? I tried placing it in a module and then I had the sheet configured to call the macro "Count_files" when data is entered into cell $G$4, but I get an error message:

Compile Error:
User-defined type not defined

and it highlights "New Scripting.FileSystemObject" in blue and highlights Line 11 in Yellow

and I don't really know what to do about it..
Appreciate any help you could prvide.
Cheers

 

[daverunt]

Ah, ok.

You have put it in the right place and called it as required.

As you go further into your VBA coding occasionally you will use code that requires other libraries.
The only clue you usually get is the kind of failure you experienced.
The issue is a missing reference to the FilesScriptingObject, which is part of the scripting runtime library.

In the VB Editor

Select Tools > References from the drop-down menu
Tick the check-box next to Microsoft Scripting Runtime.

Should work and let you run the macro.

 

[Leggs11]

Thanks A LOT daverunt!!!!! That code is working
The only issue that I am seeing with it is that it will count the correct number the first time. But if you were to delete the data in the cell and then re-enter it again (or even with different data) it does some addition and it counts higher and higher.
E.g. I enter "BLAH" into $G$4 and it counts 8 files, then I want to delete the data in cell $G$4 and enter "TEST" (which has 2 files total) the counter then goes up to 10 instead of just changing to 2 which is what I would like it to show.

Thanks again for all your help, this is a real life-saver.
Cheers

 

[daverunt]

Set count to zero before the macro is called and it should do a new count:

    Count = 0
    Call Step_Through_Folder("C:\junk\")

 

[Leggs11]

Thanks heaps daverunt!
all seems to be working perfectly now
thanks so much for all your help

 

[daverunt]

You're welcome.