List number of files in folders

[zackete]

Looking for some help to make this code suit my needs.

Goal is to list the number of files in several folders. The folder names are listed in column C and always hosted under "C:\Users\diego\test".
The number of files should be displayed in column K.

Below code works, but it ignores folders with spaces (e.g: "test folder"). Also, would be awesome if users could change the path of those folders without editing the VBA code.

Sub Count_files()
    Dim files As Variant
    
    Set files = CreateObject("System.Collections.ArrayList")
    Call GetFiles("C:\Users\diego\test", files)


    Dim searchText As String
    Dim fileCount As Integer
    
    For r = 2 To Rows.Count
        searchText = LCase(Cells(r, 3))
        'If (searchText & "" = "") Then
         If (searchText = vbNullString) Then
           Exit For
      End If
        
        fileCount = 0
        For Each f In files
            If InStr(f, searchText) > 0 Then
                fileCount = fileCount + 1
            End If
        Next
        
        Cells(r, 11) = fileCount
    Next r
    
End Sub


Sub GetFiles(sFolderName As String, vFiles As Variant)
    Set oFSO = New Scripting.FileSystemObject
    Set oFolder = oFSO.GetFolder(sFolderName)
      
    For Each oFileItem In oFolder.files
        vFiles.Add LCase(oFileItem.Name)
    Next oFileItem
 
    For Each oSubFolder In oFolder.SubFolders
        Call GetFiles(oSubFolder.Path, vFiles)
    Next oSubFolder
End Sub

 

[lrobbo314]

This code will prompt the user to select a folder and will give a message box displaying how many files are in the folder and it's subfolders. It doesn't seem to matter if there is a space in the folder name in my testing.

Sub FILECOUNT()
Dim Fold As Object
Dim subFold As Object
Dim fld As FileDialog
Dim Path As String

Set fld = Application.FileDialog(msoFileDialogFolderPicker)

With fld
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = "C:\"
    .Show
    Path = .SelectedItems(1)
End With

With CreateObject("Scripting.FileSystemObject")
    Set Fold = .getfolder(Path)
    MsgBox Fold.Files.Count & " files in " & Path & " (Root)"
    For Each subFold In Fold.subfolders
        MsgBox subFold.Files.Count & " files in " & subFold.Path & " (SubFolder)"
    Next subFold
End With
End Sub

 

[DanteAmor]

I have no problems with your code, read the folders with space perfectly.
I made some small adjustments to your code.

Sub Count_files()
  Dim files As Variant, f As Variant, r As Long
  Dim searchText As String
  Dim fileCount As Integer
  
  Set files = CreateObject("System.Collections.ArrayList")
  Call GetFiles("C:\Users\diego\test", files)
  For r = 2 To Range("C" & Rows.Count).End(xlUp).Row
    searchText = LCase(Cells(r, 3))
    fileCount = 0
    For Each f In files
      If InStr(LCase(f), searchText) > 0 Then
        fileCount = fileCount + 1
      End If
    Next
    Cells(r, 11) = fileCount
  Next r
End Sub