Macro for counting files by date range

[DaveSaint1]

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

 

[DanteAmor]

Try this:

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

 

[DaveSaint1]

Try this:

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??

 

[DanteAmor]

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

Try:

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
[COLOR=#0000ff]  Range("B9").value = n[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[DaveSaint1]

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?

 

[DanteAmor]

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:

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 [B][COLOR=#ff0000]<[/COLOR][/B]= Range("B[B][COLOR=#ff0000]6[/COLOR][/B]") Then
        n = n + 1
      End If
    End If
  Next
  Range("B9").value = n
End Sub

 

[DaveSaint1]

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

 

[DanteAmor]

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

 

[tonyrankine]

I'm sorry, I adjusted some details:

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 [B][COLOR=#ff0000]<[/COLOR][/B]= Range("B[B][COLOR=#ff0000]6[/COLOR][/B]") Then
        n = n + 1
      End If
    End If
  Next
  Range("B9").value = n
End Sub

HI Can you please share with me the file path code. what should be used in the "folder"