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!

 

[nhelmbrecht]

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

Hello! I am attempting to count the number of files in folders and subfolders by the folders. The VBA code provided does almost exactly what I would like it to do, but it returns only the folder name. I would like it to return the folder path so that I know which subfolders belong to which folders. Is there a way to either add a new column, or replace the file name with the folder path in the StartPth that is identified?

 

[Fluff]

Please start a new thread for this question. Thanks