Macro to count the number of files in folder and subfolders

[LisaLou]

Hello,

I am really hoping someone can help me with this. I have VBA code to count the numbers of files in folders but I also need to count the number of files in sub folders too which the total will be displayed in cells in the excel spreadsheet. This is the code I have for counting in folders:

Private Sub countBatches()

Dim FolderPath As String, path As String, count As Integer, dayid As String

dayid = Range("B1").Value

FolderPath = "C:\Users\Lisap\OneDrive\Desktop\Orders\" & dayid & "\Batches"

path = FolderPath & "\*"

Filename = Dir(path)

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

Range("B2").Value = count

End Sub


Please can anyone help with this? I would be most grateful!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Lisalou()
   Dim fso As Object, StartFldr As Object
   Dim StartPth As String, DayId As String
   
   DayId = Range("B1").Value
   StartPth = "C:\Users\Lisap\OneDrive\Desktop\Orders\" & DayId & "\Batches"
   
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set StartFldr = fso.GetFolder(StartPth)
   Call RecursiveFolder(fso, StartFldr, True)
End Sub
Sub RecursiveFolder(fso As Object, Fldr As Object, IncludeSubFolders As Boolean)
   Dim FldrFile As Object
   Dim SubFldr As Object
   Dim NxtRw As Long
   
   NxtRw = Range("B" & Rows.Count).End(xlUp).Row + 1
   Range("A" & NxtRw) = Fldr.Name
   Range("B" & NxtRw) = Fldr.files.Count
   
   If IncludeSubFolders Then
      For Each SubFldr In Fldr.SubFolders
         Call RecursiveFolder(fso, SubFldr, True)
      Next SubFldr
   End If
End Sub

 

[LisaLou]

Hi,

Thank you so much!

This code works great but how do I get it to put the total count of files in the sub folder to be displayed in cell B3?

I will be putting the date in cell B1 and then want the total count of files the sub folder displayed in B3?

Thanks!

 

[Fluff]

So you just want a total count of all files, rather than a count per folder?

 

[LisaLou]

Yes, when I put the date in cell B1 I want a count of all the files in the sub folders which are in the file Batches to be displayed in cell B2. When I type a different date in cell B1 I want a count of all those files in the sub folder which are in the folder called Batches for this date.

hope this makes sense?
Thanks

 

[Fluff]

In that case use

Sub Lisalou()
   Dim fso As Object, StartFldr As Object
   Dim StartPth As String, DayId As String
   
   DayId = Range("B1").Value
   StartPth = "C:\Users\Lisap\OneDrive\Desktop\Orders\" & DayId & "\Batches"
   
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set StartFldr = fso.GetFolder(StartPth)
   Call RecursiveFolder(fso, StartFldr, True)
End Sub
Sub RecursiveFolder(fso As Object, Fldr As Object, IncludeSubFolders As Boolean)
   Dim FldrFile As Object
   Dim SubFldr As Object
   Dim NxtRw As Long
   
   Range("B3") = Range("B3") + Fldr.files.Count
   
   If IncludeSubFolders Then
      For Each SubFldr In Fldr.SubFolders
         Call RecursiveFolder(fso, SubFldr, True)
      Next SubFldr
   End If
End Sub

 

[LisaLou]

Hi,

Thank you so much for all the help but I can't get this to work, any ideas?

Thanks,
Lisa

 

[Fluff]

I can't get this to work,

In what way?

 

[LisaLou]

In what way?

I run the macro but it doesn't show the number of files in cell B3 when I know there are files in the folder 'Batches' in the date I type in cell B1

 

[LisaLou]

I run the macro but it doesn't show the number of files in cell B3 when I know there are files in the folder 'Batches' in the date I type in cell B1

Sub Lisalou()
Dim fso As Object, StartFldr As Object
Dim StartPth As String, DayId As String

DayId = Range("B1").Value
StartPth = "C:\Users\Lisap\OneDrive\Desktop\Orders\" & DayId & "\Batches"

Set fso = CreateObject("Scripting.FileSystemObject")
Set StartFldr = fso.GetFolder(StartPth)
Call RecursiveFolder(fso, StartFldr, True)
End Sub


The part in blue is highlighted when I try and run the macro