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!

 

[LisaLou]

I have deleted the sheet and started again with a new excel sheet and seem to have made a bit of progress.

I am using this code but have take out the msg box line (do I need that)?

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

It doesn't seem to like this:

Set StartFldr = fso.GetFolder(StartPth)

As is highlighted in yellow

 

[Fluff]

In that case the file path is not correct. The values in col A must be the exact path for everything after the DayId.
So if you have a path like
C:\Users\Lisap\OneDrive\Desktop\Orders\110321\Queries\Completed Queries\UJAC
the value in col A must be
Queries\Completed Queries\UJAC

 

[LisaLou]

Works great!

When I enter a different date it counts up to 'Complete' and then I get this message:

And when I look at the code the same line is highlighted in yellow again:

I have checked that all the values are correct because it works when I enter the date 110321.

Thanks so much for your help today.

 

[Fluff]

Do you have a path called "C:\Users\Lisap\OneDrive\Desktop\Orders\120321\Complete"?

 

[LisaLou]

Do you have a path called "C:\Users\Lisap\OneDrive\Desktop\Orders\120321\Complete"?

Yes I do:

C:\Users\Lisap\OneDrive\Desktop\Orders\120321\Complete

 

[LisaLou]

I have different dates but the folder structure is the same and I would like to change the date in B1 and the counts will change depending on what files are in the folders.

 

[Fluff]

Check that the values in col A don't have any leading/trailing spaces.

 

[LisaLou]

Check that the values in col A don't have any leading/trailing spaces.

I have checked and there are no spaces...

 

[Fluff]

In that case I don't know & without access to your computer, there's not much more I can do.

 

[Fluff]

Just noticed the image you added to post#56 & that looks as though you are looking in the wrong place, should you be looking in
C:\Users\Lisap\OneDrive\Desktop\Orders\120321\Complete
or
C:\Users\Lisap\Desktop\Orders\120321\Complete