Directory count occurrences

[jujubeans69]

I am currenlty using a "simple" macro to count the number of files in a particular folder. I was wondering if anyone can help me modify/adjust it so it only counts the number of files with today's date. Many thanks in advance!

My script:

Sub DTSx()

Dim srch1 As String, found1 As Integer, i1 As Integer
For i1 = 4 To 4
     srch1 = "\\root\inbound\" & "*.835"
     found1 = 0
     If Dir(srch1) <> "" Then 'is there such a file?
          Do
               found1 = found1 + 1
          Loop While Dir() <> "" 'are there more such files
     End If
     Sheets("DTS Xmissions").Range("C" & i1) = found1
Next i1

I was able to find this using google, but am not sure how to incorporate it, or even if it would work.

Dim csvFiles = From csv In Directory.EnumerateFiles("DirectoryPath", "*Test.csv", IO.SearchOption.AllDirectories)
               Where File.GetCreationTime(csv).Date = Date.Today
For Each csvPath In csvFiles
    Console.WriteLine(csvPath)
Next

If you are on .NET 2 as commented, you don't have Linq and <CODE>Directory.EnumerateFiles</CODE>, then use <CODE>GetFiles</CODE> and loops:

<CODE>

Dim csvFilesToday = New List(Of String)
For Each csv In Directory.GetFiles("DirectoryPath", "*Test.csv", IO.SearchOption.AllDirectories)
    If File.GetCreationTime(csv).Date = Date.Today Then
        csvFilesToday.Add(csv)
    End If
Next

</CODE>

 

[xenou]

Adapting Fazza's function you could write the procedure like this:

You must include the enum DateCheckType at the top before any subroutines
You must include the function FileCountByDate along with Sub DTSx()
Fazza's function provide expanded date check options. Here we only check for the "same day". It is assumed that the date you are checking for is in cell B2.

[COLOR="Navy"]Option Explicit[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Enum[/COLOR] DateCheckType
  dcOnThisDate
  dcOnOrBeforeThisDate
  dcOnOrAfterThisDate
  dcOnOrBetweenTheseDates
  dcOutsideTheseDates
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Enum[/COLOR]

[COLOR="Navy"]Sub[/COLOR] DTSx()

[COLOR="Navy"]Dim[/COLOR] srchPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] srchPattern [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] srchType [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] srchDate [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]
[COLOR="Navy"]Dim[/COLOR] destRange [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="SeaGreen"]'------------------------------------[/COLOR]
    [COLOR="SeaGreen"]'PARAMETERS[/COLOR]
    
    [COLOR="SeaGreen"]'//Date To Search[/COLOR]
    srchDate = Range("B2").Value
    
    [COLOR="SeaGreen"]'//Search Type (choose 1)[/COLOR]
    srchType = dcOnThisDate
    [COLOR="SeaGreen"]'srchType = dcOnOrBeforeThisDate[/COLOR]
    [COLOR="SeaGreen"]'srchType = dcOnOrAfterThisDate[/COLOR]
    [COLOR="SeaGreen"]'srchType = dcOnOrBetweenTheseDates[/COLOR]
    [COLOR="SeaGreen"]'srchType = dcOutsideTheseDates[/COLOR]
    [COLOR="SeaGreen"]'------------------------------------[/COLOR]
    
    
    destRange = "C4"
    srchPath = "\\mb05a\ftproot\dts\officeally\inbound"
    srchPattern = "*_ERA_835_5010_" & Format(Range("B2"), "yyyymmdd") & ".835"
    Sheets("DTS Xmissions").Range(destRange) = FileCountByDate(srchPath, srchPattern, srchType, srchDate)
    

    destRange = "D4"
    srchPath = "\\mb05a\ftproot\dts\officeally\inbound\history"
    srchPattern = "*_ERA_835_5010_" & Format(Range("B2"), "yyyymmdd") & ".835"
    Sheets("DTS Xmissions").Range(destRange).Value = FileCountByDate(srchPath, srchPattern, srchType, srchDate)

    destRange = "E4"
    srchPath = "\\mb03a\mbxc01c\q\payor\ally\in\wf"
    srchPattern = Format(Range("B2"), "mmdd") & "_???.ARA"
    Sheets("DTS Xmissions").Range(destRange).Value = FileCountByDate(srchPath, srchPattern, srchType, srchDate)

    [COLOR="SeaGreen"]'and so on ...[/COLOR]
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] FileCountByDate(ByVal FilePathToCheck [COLOR="Navy"]As[/COLOR] String, _
    [COLOR="Navy"]ByVal[/COLOR] FilePatternToCheck [COLOR="Navy"]As[/COLOR] String, [COLOR="Navy"]ByVal[/COLOR] MyCheckType [COLOR="Navy"]As[/COLOR] DateCheckType, _
        [COLOR="Navy"]ByVal[/COLOR] DateToCheck [COLOR="Navy"]As[/COLOR] Date, [COLOR="Navy"]Optional[/COLOR] [COLOR="Navy"]ByVal[/COLOR] LaterDateToCheck [COLOR="Navy"]As[/COLOR] Date) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]


  [COLOR="Navy"]Dim[/COLOR] strFileName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
  [COLOR="Navy"]Dim[/COLOR] lngDateToCheck [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
  [COLOR="Navy"]Dim[/COLOR] lngLaterDateToCheck [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]


  FileCountByDate = 0
  lngDateToCheck = CLng(DateToCheck)
  lngLaterDateToCheck = CLng(LaterDateToCheck)


  strFileName = Dir(FilePathToCheck & Application.PathSeparator & FilePatternToCheck)
  [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] Len(strFileName) > 0
      
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] MyCheckType
    
      [COLOR="Navy"]Case[/COLOR] dcOnThisDate: [COLOR="Navy"]If[/COLOR] Int(FileDateTime(FilePathToCheck & Application.PathSeparator & strFileName)) = lngDateToCheck [COLOR="Navy"]Then[/COLOR] FileCountByDate = FileCountByDate + 1
    
      [COLOR="Navy"]Case[/COLOR] dcOnOrBeforeThisDate: [COLOR="Navy"]If[/COLOR] Int(FileDateTime(FilePathToCheck & Application.PathSeparator & strFileName)) <= lngDateToCheck [COLOR="Navy"]Then[/COLOR] FileCountByDate = FileCountByDate + 1
      
      [COLOR="Navy"]Case[/COLOR] dcOnOrAfterThisDate: [COLOR="Navy"]If[/COLOR] Int(FileDateTime(FilePathToCheck & Application.PathSeparator & strFileName)) >= lngDateToCheck [COLOR="Navy"]Then[/COLOR] FileCountByDate = FileCountByDate + 1
      
      [COLOR="Navy"]Case[/COLOR] dcOnOrBetweenTheseDates: [COLOR="Navy"]If[/COLOR] Int(FileDateTime(FilePathToCheck & Application.PathSeparator & strFileName)) >= lngDateToCheck _
        [COLOR="Navy"]And[/COLOR] Int(FileDateTime(FilePathToCheck & Application.PathSeparator & strFileName)) <= lngLaterDateToCheck [COLOR="Navy"]Then[/COLOR] FileCountByDate = FileCountByDate + 1
      
      [COLOR="Navy"]Case[/COLOR] dcOutsideTheseDates: [COLOR="Navy"]If[/COLOR] Int(FileDateTime(FilePathToCheck & Application.PathSeparator & strFileName)) < lngDateToCheck _
        [COLOR="Navy"]And[/COLOR] Int(FileDateTime(FilePathToCheck & Application.PathSeparator & strFileName)) > lngLaterDateToCheck [COLOR="Navy"]Then[/COLOR] FileCountByDate = FileCountByDate + 1
      
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Select[/COLOR]
    
    strFileName = Dir
  [COLOR="Navy"]Loop[/COLOR]


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

 

[jujubeans69]

Thank you for adapting his code. It was very confusing, and I did not know where to place the outputs. I will try this one, and see if it speeds up the search.

 

[jujubeans69]

The script errors, and highlights the line below:

strFileName = Dir(FilePathToCheck & Application.PathSeparator & FilePatternToCheck)

 

[xenou]

You might need to post more of your code. Basically we need to see what the variables are for FilePathToCheck and FilePatternToCheck. That's probably where the problem is.