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]

What error do you get?

 

[LisaLou]

Hi,

Sorry to bother you again. The above code does work but when I type the date and press enter is there a way that this will refresh the spreadsheet and count all the files etc without having to click on macro then run?

Thanks again for the help, I appreciate it.

Lisa

 

[Fluff]

If you mean use

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim fso As Object, StartFldr As Object
   Dim StartPth As String, DayId As String
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B1" Then
      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 If
End Sub

This needs to go in the relevant sheet module

 

[LisaLou]

Hi,

Sorry to bother you again. The above code does work but when I type the date and press enter is there a way that this will refresh the spreadsheet and count all the files etc without having to click on macro then run?

Thanks again for the help, I appreciate it.

Lisa

I just wanted to clarify that when I enter the date (110321) in cell B1, I want the macro to run and count all the files in the subfolders within the folder 'Batches' (only in the date 110321) and then put that count in cell B3.

Thanks again.

 

[Fluff]

Isn't that what it does?

 

[LisaLou]

So sorry..... I am getting this message:

 

[Fluff]

That means you all ready have a change event in that sheet. As you can only have one you will need to put them together.

 

[LisaLou]

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim fso As Object, StartFldr As Object
   Dim StartPth As String, DayId As String
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B1" Then
      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 If
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("B2") = Range("B2") + Fldr.Files.count
   
   If IncludeSubFolders Then
      For Each SubFldr In Fldr.SubFolders
         Call RecursiveFolder(fso, SubFldr, True)
      Next SubFldr
   End If
End Sub

This is all the code I have in the spreadsheet. Am I missing anything?

Thanks,
Lisa

 

[Fluff]

You must have more code in that sheet module, otherwise you would not get that error.

 

[LisaLou]

You must have more code in that sheet module, otherwise you would not get that error.

Thank you so much for your patience, I have got it working!