Results 1 to 8 of 8

Thread: Macro for counting files by date range

  1. #1
    New Member
    Join Date
    Sep 2019
    Location
    UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro for counting files by date range

    Hi Everyone,

    I need help.......

    I am trying to create a macro which will count the number of .pdf files within a specified folder based on a a date range.

    The dates are set in cells b5 and b6 (start date and end date) and the file location is in a cell named "Folder"

    I have been watching VBA videos all day to try and figure this one out but i am no closer.

    Thanks in advance

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,192
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro for counting files by date range

    Try this:

    Code:
    Sub counting_files_by_date()
      Dim wPath As String, wFile As Object, wFso As Object, n As Long
      wPath = Range("folder")
      If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
      If Dir(wPath, vbDirectory) = "" Then
        MsgBox "Folder does not exist"
        Exit Sub
      End If
      Set wFso = CreateObject("Scripting.FileSystemObject")
      For Each wFile In wFso.GetFolder(wPath).Files
        If LCase(Right(wFile, 3)) = "pdf" Then
          If wFile.Datecreated >= Range("B5") And wFile.Datecreated >= Range("B5") Then
            n = n + 1
          End If
        End If
      Next
      MsgBox "Number of PDF files: " & n, vbInformation, "COUNT PDF"
    End Sub
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Sep 2019
    Location
    UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro for counting files by date range

    Quote Originally Posted by DanteAmor View Post
    Try this:

    Code:
    Sub counting_files_by_date()
      Dim wPath As String, wFile As Object, wFso As Object, n As Long
      wPath = Range("folder")
      If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
      If Dir(wPath, vbDirectory) = "" Then
        MsgBox "Folder does not exist"
        Exit Sub
      End If
      Set wFso = CreateObject("Scripting.FileSystemObject")
      For Each wFile In wFso.GetFolder(wPath).Files
        If LCase(Right(wFile, 3)) = "pdf" Then
          If wFile.Datecreated >= Range("B5") And wFile.Datecreated >= Range("B5") Then
            n = n + 1
          End If
        End If
      Next
      MsgBox "Number of PDF files: " & n, vbInformation, "COUNT PDF"
    End Sub


    Thank you so much, that is great!! 1 minor tweak, would it be possible for that count figure to populate into cell b9??

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,192
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro for counting files by date range

    Quote Originally Posted by DaveSaint1 View Post
    Thank you so much, that is great!! 1 minor tweak, would it be possible for that count figure to populate into cell b9??

    Try:

    Code:
    Sub counting_files_by_date()
      Dim wPath As String, wFile As Object, wFso As Object, n As Long
      wPath = Range("folder")
      If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
      If Dir(wPath, vbDirectory) = "" Then
        MsgBox "Folder does not exist"
        Exit Sub
      End If
      Set wFso = CreateObject("Scripting.FileSystemObject")
      For Each wFile In wFso.GetFolder(wPath).Files
        If LCase(Right(wFile, 3)) = "pdf" Then
          If wFile.Datecreated >= Range("B5") And wFile.Datecreated >= Range("B5") Then
            n = n + 1
          End If
        End If
      Next
      Range("B9").value = n
    End Sub
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Sep 2019
    Location
    UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro for counting files by date range

    Thanks again, it blows my mind that people can write this so quickly. It took me the best part of the day to make a date picker.

    It seems to mostly work, however the count doesn't seem right when i double check within the folder itself?

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,192
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro for counting files by date range

    Quote Originally Posted by DaveSaint1 View Post
    Thanks again, it blows my mind that people can write this so quickly. It took me the best part of the day to make a date picker.

    It seems to mostly work, however the count doesn't seem right when i double check within the folder itself?

    I'm sorry, I adjusted some details:

    Code:
    Sub counting_files_by_date()
      Dim wPath As String, wFile As Object, wFso As Object, n As Long
      wPath = Range("folder")
      If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
      If Dir(wPath, vbDirectory) = "" Then
        MsgBox "Folder does not exist"
        Exit Sub
      End If
      Set wFso = CreateObject("Scripting.FileSystemObject")
      For Each wFile In wFso.GetFolder(wPath).Files
        If LCase(Right(wFile, 3)) = "pdf" Then
          If wFile.Datecreated >= Range("B5") And wFile.Datecreated <= Range("B6") Then
            n = n + 1
          End If
        End If
      Next
      Range("B9").value = n
    End Sub
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Sep 2019
    Location
    UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro for counting files by date range

    Thank you so much for your help, this is amazing!

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,192
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro for counting files by date range

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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