PDA

View Full Version : VBA with counting files



karolis
10-10-2018, 04:49 AM
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

JKwan
10-10-2018, 09:13 AM
you never reset your count. it is inside your loop.

p45cal
10-10-2018, 04:53 PM
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.

karolis
10-10-2018, 09:52 PM
Thank You! It helped very much :)