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]

In that case delete the existing code & use

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim fso As Object, StartFldr As Object
   Dim StartPth As String, DayId As String
   Dim Cl As Range
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B1" Then
      DayId = Range("B1").Value
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         StartPth = "C:\Users\Lisap\OneDrive\Desktop\Orders\" & DayId & "\" & Cl.Value
         Set fso = CreateObject("Scripting.FileSystemObject")
         Set StartFldr = fso.GetFolder(StartPth)
         Call RecursiveFolder(fso, StartFldr, Cl.Row, True)
      Next Cl
   End If
End Sub

Sub RecursiveFolder(fso As Object, Fldr As Object, Rw As Long, IncludeSubFolders As Boolean)
   Dim FldrFile As Object
   Dim SubFldr As Object
   Dim NxtRw As Long

   Range("B" & Rw) = Range("B" & Rw) + Fldr.Files.Count

   If IncludeSubFolders Then
      For Each SubFldr In Fldr.SubFolders
         Call RecursiveFolder(fso, SubFldr, Rw, True)
      Next SubFldr
   End If
End Sub

 

[LisaLou]

Thank you so much but I can't get it working.

 

[Fluff]

In what way?

 

[LisaLou]

In what way?

I type the date in the spreadsheet press enter and it doesn't do anything and if I run the macro it still doesn't do anything.

 

[Fluff]

Do you have file paths like
"C:\Users\Lisap\OneDrive\Desktop\Orders\" & DayId & "\Unallocated Batches\"
and
"C:\Users\Lisap\OneDrive\Desktop\Orders\" & DayId & "\Completed Queries UJAC\"

 

[LisaLou]

Do you have file paths like
"C:\Users\Lisap\OneDrive\Desktop\Orders\" & DayId & "\Unallocated Batches\"
and
"C:\Users\Lisap\OneDrive\Desktop\Orders\" & DayId & "\Completed Queries UJAC\"

Yes I do:

C:\Users\Lisap\OneDrive\Desktop\Orders\110321\Batches\001

C:\Users\Lisap\OneDrive\Desktop\Orders\110321\Queries\Completed Queries\UJAC

C:\Users\Lisap\OneDrive\Desktop\Orders\110321\Queries\Completed Queries\ULPF

C:\Users\Lisap\OneDrive\Desktop\Orders\110321\UJAC\002

These are just some examples

 

[Fluff]

Those are not the same. The values in col A will need to be the exact path after the DayId.

 

[LisaLou]

I have changed the labels in column A to match the path names

Date

Batches

Completed Orders

Completed Queries UJAC

Completed Queries ULPF

Completed Queries USAE

Printed

Queries To Do

UJAC

ULPF

USAE

In the 'Batches Folder' there are numerical named folders such as 001 which have files in them that I would like counting and the total of all the files in the subfolders within 'Batches' entered into cell B2.

Hope this makes sense?

 

[Fluff]

And what happens?

 

[LisaLou]

And what happens?

Hi,

Sorry the above is incorrect, this is all the labels changed to match what is after dayid:

Date

Batches

Complete

Queries Completed Queries UJAC

Queries Completed Queries ULPF

Queries Completed Queries USAE

Queries Printed

Queries

UJAC

ULPF

USAE

When I put the date in and press enter nothing happens and when I run the Macro I get this message:

Thanks,