Counting .txt files in folder that match date given

[mrtim2232]

Afternoon All,

I'm after some help creating a way either by macro or custom function to count the number of .txt files in a folder that were created on a date given in A1. It will then display the number of files in A2. There are a lot of different filepaths however once they are set they will not change.

 

[Joe4]

We can create our own User Defined Function to to count the number of files in a particular folder, with a particular file extension, created on a particular date like this:

Function CountFiles(myDir As String, myExt As String, myDate As Date) As Long

    Dim myFullFile As String
    Dim strFile As String
    Dim myCount As Long
    Dim objFile As Object
    
'   Create full path/extension to look for
    If Right(myDir, 1) <> "\" Then myDir = myDir & "\"
    If Left(myExt, 1) <> "." Then myExt = myExt & "."
    myFullFile = myDir & "*" & myExt
    
'   Loop through folder
    strFile = Dir(myFullFile)
    Do While Len(strFile) > 0
'       Set file to an object
        Set objFile = CreateObject("Scripting.FileSystemObject")
'       Check to see if created date on file matches entered date
        If Int(objFile.GetFile(myDir & "\" & strFile).DateCreated) = myDate Then
            myCount = myCount + 1
        End If
'       Move on to next file
        Set objFile = Nothing
        strFile = Dir
    Loop
    
'   Set final count
    CountFiles = myCount
    
End Function

We can then call/use that function in a VBA procedure/macro like this, for example:

Sub Test()

    Dim dir1 As String
    Dim ext1 As String
    Dim dte1 As Date

'   Set the directory to look in
    dir1 = "C:\Temp"
    
'   Set the extension to look for
    ext1 = "txt"

'   Set the date you want to check
    dte1 = DateSerial(2019, 7, 25)

'   Count files and return to screen
    MsgBox CountFiles(dir1, ext1, dte1)

End Sub

So, if you had a list of directories that you wanted to look through, you could loop through the list (either storing the list in an Excel sheet, or in an array right in VBA).

 

[mrtim2232]

Morning Joe,

Thanks for your help it works to a degree however I think I must be putting the date in the wrong format as when I don't put a date in it counts every file in the folder correctly but when I put the date of one of the files in it returns 0. Is there a specific way that I have to have the date?

 

[Joe4]

Thanks for your help it works to a degree however I think I must be putting the date in the wrong format as when I don't put a date in it counts every file in the folder correctly but when I put the date of one of the files in it returns 0. Is there a specific way that I have to have the date?

You haven't shown us how you are putting in the date (what it looks like).

In my VBA code, the date is here:

'   Set the date you want to check
    dte1 = DateSerial(2019, 7, 25)

Note that the format of DateSerial is:
DateSerial(year, month, day)

You can also use DateValue, but I am always leery about that one, especially if you are in a European country (as VBA uses American date formats).