List number of files in folders

zackete

New Member
Joined
Apr 15, 2012
Messages
42
Office Version
  1. 365
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.

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
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.

Code:
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
 
Last edited:
Upvote 0
I have no problems with your code, read the folders with space perfectly.
I made some small adjustments to your code.

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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top