-
VBA with counting files
Hi :)
So in work we have this folder with multiple folders in it. Each of them have different number of various files. We need to know specifically how each folder has files. In excel we have a table with folder names. So in our code the Range D7: 14 are the exact location of the specific folder and the result should appear next to it (the number of files).
The problem I am facing is that the first folder shows the correct number, but the second adds the files from the previous folder and so on. I cant see my mistake. Please help!
Here is the code:
Dim FolderPath As String, Path As String, count As Integer
Set MyRange = Range("D7:14")
For Each c In MyRange
Path = c & "\*.xlsx"
FileName = Dir(Path)
Do While FileName <> ""
count = count + 1
FileName = Dir()
Loop
c.Offset(0, 1).Value = count
Next c
-
you never reset your count. it is inside your loop.
-
Code:
Dim FolderPath As String, Path As String, count As Integer
Set MyRange = Range("D7:14") '<<< doubt this line'll work.
For Each c In MyRange
count = 0
Path = c & "\*.xlsx"
Filename = Dir(Path)
Do While Filename <> ""
count = count + 1
Filename = Dir()
Loop
c.Offset(0, 1).Value = count
Next c
but check it gives the right count.
-
Thank You! It helped very much :)