Consulting

Results 1 to 20 of 20

Thread: count files extensions files for each month based on modified date

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    count files extensions files for each month based on modified date

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •