Hi,
I need fixing code to count how many files extensions for each month based on modified date in folders and subfolders and sub-subfolders
I put expected result should be in sheet1
Sub FindCountU()
Dim acs As Worksheet
Dim n As Workbook
Dim FolderName As String, Filename As String, ext$
Dim i As Integer, a$(), cnt&, j&, t&, ct&, f, fs
Dim dict As Object, ltst As Date, fin As Date
Set fs = CreateObject("Scripting.FileSystemObject")
Set dict = CreateObject("scripting.dictionary")
FolderName = "C:\Users\ABB\Desktop\data" & "\"
Filename = Dir(FolderName & "*.*")
Do While Filename <> ""
cnt = cnt + 1
Filename = Dir()
Loop
ReDim a(1 To cnt, 1 To 2)
Filename = Dir(FolderName & "*.*")
Do While Filename <> ""
i = i + 1
Set f = fs.GetFile(FolderName & Filename)
ext = Mid(Filename, InStr(1, Filename, ".") + 1)
a(i, 1) = ext
a(i, 2) = f.datelastmodified
If Not dict.exists(ext) Then
dict.Add ext, i
End If
Filename = Dir()
Loop
ct = 0
ltst = #1/1/2001#
fin = #1/1/2001#
For j = 0 To dict.Count - 1
For t = LBound(a) To UBound(a)
If dict.keys()(j) = a(t, 1) Then
ct = ct + 1
If Format(a(t, 2), "mm/dd/yyyy") > Format(ltst, "mm/dd/yyyy") Then
ltst = Format(a(t, 2), "mm/dd/yyyy")
fin = Format(a(t, 2), "mm/dd/yyyy")
End If
End If
Next t
Sheets("SHEET1").Cells(j + 2, 1).Value = j + 1
Sheets("SHEET1").Cells(j + 2, 2).Value = dict.keys()(j)
Sheets("SHEET1").Cells(j + 2, 3).Value = ct
Sheets("SHEET1").Cells(j + 2, 4).Value = UCase(Format(fin, "mmm"))
ct = 0
ltst = #1/1/2001#
fin = #1/1/2001#
Next j
End Sub
thanks