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]

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

I don't get where or what I am supposed to change. I don't understand why it would work for 110321 and not 120321.

Again, I appreciate your help.

Lisa

 

[Fluff]

The image you posted showing the folders looks to be looking at "C:\Users\Lisap\Desktop\Orders\120321\Complete" which is not where the code is looking.
Therefore where should the code be looking?

 

[LisaLou]

The image you posted showing the folders looks to be looking at "C:\Users\Lisap\Desktop\Orders\120321\Complete" which is not where the code is looking.
Therefore where should the code be looking?

I need it to look in the orders folder and count what is in the folders and subfolders when I enter the date and then when I enter a new date it will refresh the counts and change them accordingly.

 

[Fluff]

Yes but where exactly are those folders?

 

[LisaLou]

Yes but where exactly are those folders?

In the orders folder there are some 'Date' folders:

Each 'date' folder has the exact same folder structure:

In 'Batches' there are sub folders labelled: 001, 002, 003.... and there are files in these sub folders

The same is for the rest of the folders.... Complete will just have files. Queries has sub folders and files...

The code works great for 110321:

As the folder structure is exactly the same for each 'date' folder I should be able to type a different date such as 120321 and the code will loop through the folders, subfolders etc to give new counts.

I really hope I am making sense?

 

[Fluff]

Try using this & see if it helps

         StartPth = "C:\Users\Lisap\Desktop\Orders\" & DayId & "\" & Cl.Value

 

[LisaLou]

Try using this & see if it helps

         StartPth = "C:\Users\Lisap\Desktop\Orders\" & DayId & "\" & Cl.Value



[/QUOTE]

I tried this and got this highlighted in the code:

 

[Fluff]

As I cannot see your computer or where the folders are located, there is not much more I can do.
You need to check that all the various paths are correct.

 

[LisaLou]

As I cannot see your computer or where the folders are located, there is not much more I can do.
You need to check that all the various paths are correct.

Thank you so much for the help.

 

[Fluff]

You're welcome