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]

Okay, well the reason I keep asking for you complete code is to get all of the details. Can you not post the full code?

 

[jujubeans69]

xenou, that is it. Aside from the beginning Sub DTSX() line, and the ending End Sub line, the code above is just repeated over for different folders (srch## line). Below is what the spreadsheet looks like, and each (4 sets of code) pertains to one row, trying to track the files through processing points C and D, then E and F.

 

[xenou]

Aside from the beginning Sub DTSX() line, and the ending End Sub line, the code above is just repeated over for different folders (srch## line).

Okay, so you say it is repeated over for different folders? How many times? How do you script the "repeats"? Again - the complete code. Your code as written is just awful so I want to see it all so we can trim it down to what is necessary, no more.

 

[jujubeans69]

The same coding is repeated 4x for each column, from srch1 to srch102. The file names for the searches in different folders are different, so I am not sure how this would be cut down.

Sub DTSx()

Dim srch1 As String, found1 As Integer, i1 As Integer
For i1 = 4 To 4
    srch1 = "\\mb05a\ftproot\dts\officeally\inbound\" & "*_ERA_835_5010_" & Format(Range("B2"), "yyyymmdd") & ".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

Dim srch2 As String, found2 As Integer, i2 As Integer
For i2 = 4 To 4
    srch2 = "\\mb05a\ftproot\dts\officeally\inbound\history\" & "*_ERA_835_5010_" & Format(Range("B2"), "yyyymmdd") & ".835"
    found2 = 0
    If Dir(srch2) <> "" Then   'is there such a file?
        Do
             found2 = found2 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i2) = found2
Next i2

Dim srch3 As String, found3 As Integer, i3 As Integer
For i3 = 4 To 4
    srch3 = "\\mb03a\mbxc01c\q\payor\ally\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found3 = 0
    If Dir(srch3) <> "" Then   'is there such a file?
        Do
             found3 = found3 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i3) = found3
Next i3

Dim srch4 As String, found4 As Integer, i4 As Integer
For i4 = 4 To 4
    srch4 = "\\mb03a\mbxc01c\q\payor\ally\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found4 = 0
    If Dir(srch4) <> "" Then   'is there such a file?
        Do
             found4 = found4 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i4) = found4
Next i4

Dim srch5 As String, found5 As Integer, i5 As Integer
For i5 = 4 To 4
    srch5 = "\\mb05a\ftproot\dts\officeally\inbound\" & "*_ERA_835_5010_" & Format(Range("B1"), "yyyymmdd") & ".835"
    found5 = 0
    If Dir(srch5) <> "" Then   'is there such a file?
        Do
             found5 = found5 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("G" & i5) = found5
Next i5

Dim srch6 As String, found6 As Integer, i6 As Integer
For i6 = 4 To 4
    srch6 = "\\mb05a\ftproot\dts\officeally\inbound\history\" & "*_ERA_835_5010_" & Format(Range("B1"), "yyyymmdd") & ".835"
    found6 = 0
    If Dir(srch6) <> "" Then   'is there such a file?
        Do
             found6 = found6 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("H" & i6) = found6
Next i6

Dim srch7 As String, found7 As Integer, i7 As Integer
For i7 = 4 To 4
    srch7 = "\\mb03a\mbxc01c\q\payor\ally\in\wf\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found7 = 0
    If Dir(srch7) <> "" Then   'is there such a file?
        Do
             found7 = found7 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("I" & i7) = found7
Next i7

Dim srch8 As String, found8 As Integer, i8 As Integer
For i8 = 4 To 4
    srch8 = "\\mb03a\mbxc01c\q\payor\ally\in\wf\history\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found8 = 0
    If Dir(srch8) <> "" Then   'is there such a file?
        Do
             found8 = found8 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("J" & i8) = found8
Next i8
 
Dim srch9 As String, found9 As Integer, i9 As Integer
For i9 = 6 To 6
    srch9 = "\\mb05a\ftproot\dts\aetna\inbound\" & "HEALTHLOGIC.x12-835-5010." & Format(Range("B2"), "yyyymmdd") & ".*"
    found9 = 0
    If Dir(srch9) <> "" Then   'is there such a file?
        Do
             found9 = found9 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i9) = found9
Next i9

Dim srch10 As String, found10 As Integer, i10 As Integer
For i10 = 6 To 6
    srch10 = "\\mb05a\ftproot\dts\aetna\inbound\history\" & "HEALTHLOGIC.x12-835-5010." & Format(Range("B2"), "yyyymmdd") & ".*"
    found10 = 0
    If Dir(srch10) <> "" Then   'is there such a file?
        Do
             found10 = found10 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i10) = found10
Next i10

Dim srch11 As String, found11 As Integer, i11 As Integer
For i11 = 6 To 6
    srch11 = "\\mb03a\mbxc01c\q\payor\cmae1\in\wf\" & "13" & Format(Range("B2"), "mmdd") & ".ARA"
    found11 = 0
    If Dir(srch11) <> "" Then   'is there such a file?
        Do
             found11 = found11 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i11) = found11
Next i11

Dim srch12 As String, found12 As Integer, i12 As Integer
For i12 = 6 To 6
    srch12 = "\\mb03a\mbxc01c\q\payor\cmae1\in\wf\history\" & "13" & Format(Range("B2"), "mmdd") & ".ARA"
    found12 = 0
    If Dir(srch12) <> "" Then   'is there such a file?
        Do
             found12 = found12 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i12) = found12
Next i12

Dim srch13 As String, found13 As Integer, i13 As Integer
For i13 = 50 To 50
    srch13 = "\\mb05a\ftproot\dts\swhp\inbound\" & "S835_*.edi"
    found13 = 0
    If Dir(srch13) <> "" Then   'is there such a file?
        Do
             found13 = found13 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i13) = found13
Next i13

Dim srch14 As String, found14 As Integer, i14 As Integer
For i14 = 50 To 50
    srch14 = "\\mb05a\ftproot\dts\swhp\inbound\history\" & "S835_*.edi"
    found14 = 0
    If Dir(srch14) <> "" Then   'is there such a file?
        Do
             found14 = found14 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i14) = found14
Next i14

Dim srch15 As String, found15 As Integer, i15 As Integer
For i15 = 50 To 50
    srch15 = "\\mb03a\mbxc01c\q\payor\swhp1\in\wf\" & "S835_*.ARA"
    found15 = 0
    If Dir(srch15) <> "" Then   'is there such a file?
        Do
             found15 = found15 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i15) = found15
Next i15

Dim srch16 As String, found16 As Integer, i16 As Integer
For i16 = 50 To 50
    srch16 = "\\mb03a\mbxc01c\q\payor\swhp1\in\wf\history\" & "S835_*.ARA"
    found16 = 0
    If Dir(srch16) <> "" Then   'is there such a file?
        Do
             found16 = found16 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i16) = found16
Next i16

Dim srch17 As String, found17 As Integer, i17 As Integer
For i17 = 8 To 8
    srch17 = "\\mb05a\ftproot\dts\anthem_ca\inbound\" & "HP" & Format(Range("B2"), "yyyymmdd") & "*.835"
    found17 = 0
    If Dir(srch17) <> "" Then   'is there such a file?
        Do
             found17 = found17 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i17) = found17
Next i17

Dim srch18 As String, found18 As Integer, i18 As Integer
For i18 = 8 To 8
    srch18 = "\\mb05a\ftproot\dts\anthem_ca\inbound\history\history\" & "HP" & Format(Range("B2"), "yyyymmdd") & "*.835"
    found18 = 0
    If Dir(srch18) <> "" Then   'is there such a file?
        Do
             found18 = found18 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i18) = found18
Next i18

Dim srch19 As String, found19 As Integer, i19 As Integer
For i19 = 8 To 8
    srch19 = "\\mb03a\mbxc01c\q\payor\cabx1\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found19 = 0
    If Dir(srch19) <> "" Then   'is there such a file?
        Do
             found19 = found19 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i19) = found19
Next i19

Dim srch20 As String, found20 As Integer, i20 As Integer
For i20 = 8 To 8
    srch20 = "\\mb03a\mbxc01c\q\payor\cabx1\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found20 = 0
    If Dir(srch20) <> "" Then   'is there such a file?
        Do
             found20 = found20 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i20) = found20
Next i20

Dim srch21 As String, found21 As Integer, i21 As Integer
For i21 = 12 To 12
    srch21 = "\\mb05a\ftproot\dts\anthem_midwest\inbound\" & "HP" & Format(Range("B2"), "yyyymmdd") & "*.835"
    found21 = 0
    If Dir(srch21) <> "" Then   'is there such a file?
        Do
             found21 = found21 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i21) = found21
Next i21

Dim srch22 As String, found22 As Integer, i22 As Integer
For i22 = 12 To 12
    srch22 = "\\mb05a\ftproot\dts\anthem_midwest\inbound\history\" & "HP" & Format(Range("B2"), "yyyymmdd") & "*.835"
    found22 = 0
    If Dir(srch22) <> "" Then   'is there such a file?
        Do
             found22 = found22 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i22) = found22
Next i22

Dim srch23 As String, found23 As Integer, i23 As Integer
For i23 = 12 To 12
    srch23 = "\\mb03a\mbxc01c\q\payor\mobx1\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found23 = 0
    If Dir(srch23) <> "" Then   'is there such a file?
        Do
             found23 = found23 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i23) = found23
Next i23

Dim srch24 As String, found24 As Integer, i24 As Integer
For i24 = 12 To 12
    srch24 = "\\mb03a\mbxc01c\q\payor\mobx1\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found24 = 0
    If Dir(srch24) <> "" Then   'is there such a file?
        Do
             found24 = found24 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i24) = found24
Next i24

Dim srch25 As String, found25 As Integer, i25 As Integer
For i25 = 14 To 14
    srch25 = "\\mb05a\ftproot\dts\anthem_ny\inbound\" & "HP" & Format(Range("B2"), "yyyymmdd") & "*.835"
    found25 = 0
    If Dir(srch25) <> "" Then   'is there such a file?
        Do
             found25 = found25 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i25) = found25
Next i25

Dim srch26 As String, found26 As Integer, i26 As Integer
For i26 = 14 To 14
    srch26 = "\\mb05a\ftproot\dts\anthem_ny\inbound\history\" & "HP" & Format(Range("B2"), "yyyymmdd") & "*.835"
    found26 = 0
    If Dir(srch26) <> "" Then   'is there such a file?
        Do
             found26 = found26 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i26) = found26
Next i26

Dim srch27 As String, found27 As Integer, i27 As Integer
For i27 = 14 To 14
    srch27 = "\\mb03a\mbxc01c\q\payor\nybx1\in\wf\" & "13" & Format(Range("B2"), "mmdd") & "??.ARA"
    found27 = 0

    If Dir(srch27) <> "" Then   'is there such a file?
        Do
             found27 = found27 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i27) = found27
Next i27

Dim srch28 As String, found28 As Integer, i28 As Integer
For i28 = 14 To 14
    srch28 = "\\mb03a\mbxc01c\q\payor\nybx1\in\wf\history\" & "13" & Format(Range("B2"), "mmdd") & "??.ARA"
    found28 = 0
    If Dir(srch28) <> "" Then   'is there such a file?
        Do
             found28 = found28 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i28) = found28
Next i28

Dim srch29 As String, found29 As Integer, i29 As Integer
For i29 = 17 To 17
    srch29 = "\\mb05a\ftproot\dts\availity\bcbs\inbound\" & "ERA-BCBSTX*" & Format(Range("B2"), "yyyymmdd") & "????-001.era"
    found29 = 0
    If Dir(srch29) <> "" Then   'is there such a file?
        Do
             found29 = found29 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i29) = found29
Next i29

Dim srch30 As String, found30 As Integer, i30 As Integer
For i30 = 17 To 17
    srch30 = "\\mb05a\ftproot\dts\availity\bcbs\inbound\history\" & "ERA-BCBSTX*" & Format(Range("B2"), "yyyymmdd") & "????-001.era"
    found30 = 0
    If Dir(srch30) <> "" Then   'is there such a file?
        Do
             found30 = found30 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i30) = found30
Next i30

Dim srch31 As String, found31 As Integer, i31 As Integer
For i31 = 17 To 17
    srch31 = "\\mb03a\mbxc01c\q\payor\txmc1\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found31 = 0
    If Dir(srch31) <> "" Then   'is there such a file?
        Do
             found31 = found31 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i31) = found31
Next i31

Dim srch32 As String, found32 As Integer, i32 As Integer
For i32 = 17 To 17
    srch32 = "\\mb03a\mbxc01c\q\payor\txmc1\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found32 = 0
    If Dir(srch32) <> "" Then   'is there such a file?
        Do
             found32 = found32 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i32) = found32
Next i32

Dim srch33 As String, found33 As Integer, i33 As Integer
For i33 = 19 To 19
    srch33 = "\\mb05a\ftproot\dts\availity\bcbs_fl\inbound\" & "ERA-BCBS_OF_FL-" & Format(Range("B2"), "yyyymmdd") & "????-001.era"
    found33 = 0
    If Dir(srch33) <> "" Then   'is there such a file?
        Do
             found33 = found33 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i33) = found33
Next i33

Dim srch34 As String, found34 As Integer, i34 As Integer
For i34 = 19 To 19
    srch34 = "\\mb05a\ftproot\dts\availity\bcbs_fl\inbound\history\" & "ERA-BCBS_OF_FL-" & Format(Range("B2"), "yyyymmdd") & "????-001.era"
    found34 = 0
    If Dir(srch34) <> "" Then   'is there such a file?
        Do
             found34 = found34 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i34) = found34
Next i34

Dim srch35 As String, found35 As Integer, i35 As Integer
For i35 = 19 To 19
    srch35 = "\\mb03a\mbxc01c\q\payor\flbx1\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found35 = 0
    If Dir(srch35) <> "" Then   'is there such a file?
        Do
             found35 = found35 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i35) = found35
Next i35

Dim srch36 As String, found36 As Integer, i36 As Integer
For i36 = 19 To 19
    srch36 = "\\mb03a\mbxc01c\q\payor\flbx1\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found36 = 0
    If Dir(srch36) <> "" Then   'is there such a file?
        Do
             found36 = found36 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i36) = found36
Next i36

Dim srch37 As String, found37 As Integer, i37 As Integer
For i37 = 20 To 20
    srch37 = "\\mb05a\ftproot\dts\availity\humana\inbound\" & "ERA-HUMANA_INC-" & Format(Range("B2"), "yyyymmdd") & "????-001.era"
    found37 = 0
    If Dir(srch37) <> "" Then   'is there such a file?
        Do
             found37 = found37 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i37) = found37
Next i37

Dim srch38 As String, found38 As Integer, i38 As Integer
For i38 = 20 To 20
    srch38 = "\\mb05a\ftproot\dts\availity\humana\inbound\history\" & "ERA-HUMANA_INC-" & Format(Range("B2"), "yyyymmdd") & "????-001.era"
    found38 = 0
    If Dir(srch38) <> "" Then   'is there such a file?
        Do
             found38 = found38 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i38) = found38
Next i38

Dim srch39 As String, found39 As Integer, i39 As Integer
For i39 = 20 To 20
    srch39 = "\\mb03a\mbxc01c\q\payor\avlt\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found39 = 0
    If Dir(srch39) <> "" Then   'is there such a file?
        Do
             found39 = found39 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i39) = found39
Next i39

Dim srch40 As String, found40 As Integer, i40 As Integer
For i40 = 20 To 20
    srch40 = "\\mb03a\mbxc01c\q\payor\avlt\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found40 = 0
    If Dir(srch40) <> "" Then   'is there such a file?
        Do
             found40 = found40 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i40) = found40
Next i40

Dim srch41 As String, found41 As Integer, i41 As Integer
For i41 = 22 To 22
    srch41 = "\\mb05a\ftproot\dts\bcbs_la\inbound\" & "BC5010835.out." & Format(Range("B2"), "yyyymmdd") & "_*"
    found41 = 0
    If Dir(srch41) <> "" Then   'is there such a file?
        Do
             found41 = found41 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i41) = found41
Next i41

Dim srch42 As String, found42 As Integer, i42 As Integer
For i42 = 22 To 22
    srch42 = "\\mb05a\ftproot\dts\bcbs_la\inbound\history\" & "BC5010835.out." & Format(Range("B2"), "yyyymmdd") & "_*"
    found42 = 0
    If Dir(srch42) <> "" Then   'is there such a file?
        Do
             found42 = found42 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i42) = found42
Next i42

Dim srch43 As String, found43 As Integer, i43 As Integer
For i43 = 22 To 22
    srch43 = "\\mb03a\mbxc01c\q\payor\labx1\in\wf\" & "????_???.ARA"
    found43 = 0
    If Dir(srch43) <> "" Then   'is there such a file?
        Do
             found43 = found43 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i43) = found43
Next i43

Dim srch44 As String, found44 As Integer, i44 As Integer
For i44 = 22 To 22
    srch44 = "\\mb03a\mbxc01c\q\payor\labx1\in\wf\history\" & "????_???.ARA"
    found44 = 0
    If Dir(srch44) <> "" Then   'is there such a file?
        Do
             found44 = found44 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i44) = found44
Next i44

Dim srch45 As String, found45 As Integer, i45 As Integer
For i45 = 24 To 24
    srch45 = "\\mb05a\ftproot\dts\bcbs_ms\inbound\" & "5010835SHIELD.*"
    found45 = 0
    If Dir(srch45) <> "" Then   'is there such a file?
        Do
             found45 = found45 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i45) = found45
Next i45

Dim srch46 As String, found46 As Integer, i46 As Integer
For i46 = 24 To 24
    srch46 = "\\mb05a\ftproot\dts\bcbs_ms\inbound\history\" & "5010835SHIELD.*"
    found46 = 0
    If Dir(srch46) <> "" Then   'is there such a file?
        Do
             found46 = found46 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i46) = found46
Next i46

Dim srch47 As String, found47 As Integer, i47 As Integer
For i47 = 24 To 24
    srch47 = "\\mb03a\mbxc01c\q\payor\msbx1\in\wf\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found47 = 0
    If Dir(srch47) <> "" Then   'is there such a file?
        Do
             found47 = found47 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i47) = found47
Next i47

Dim srch48 As String, found48 As Integer, i48 As Integer
For i48 = 24 To 24
    srch48 = "\\mb03a\mbxc01c\q\payor\msbx1\in\wf\history\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found48 = 0
    If Dir(srch48) <> "" Then   'is there such a file?
        Do
             found48 = found48 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i48) = found48
Next i48

Dim srch49 As String, found49 As Integer, i49 As Integer
For i49 = 26 To 26
    srch49 = "\\mb05a\ftproot\dts\bcbs_nc\inbound\" & "P_O_CE835R." & Format(Range("B2"), "yyyy.mm.dd") & ".??????"
    found49 = 0
    If Dir(srch49) <> "" Then   'is there such a file?
        Do
             found49 = found49 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i49) = found49
Next i49

Dim srch50 As String, found50 As Integer, i50 As Integer
For i50 = 26 To 26
    srch50 = "\\mb05a\ftproot\dts\bcbs_nc\inbound\history\" & "P_O_CE835R." & Format(Range("B2"), "yyyy.mm.dd") & ".??????"
    found50 = 0
    If Dir(srch50) <> "" Then   'is there such a file?
        Do
             found50 = found50 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i50) = found50
Next i50

Dim srch51 As String, found51 As Integer, i51 As Integer
For i51 = 26 To 26
    srch51 = "\\mb03a\mbxc01c\q\payor\ncbx1\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found51 = 0
    If Dir(srch51) <> "" Then   'is there such a file?
        Do
             found51 = found51 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i51) = found51
Next i51

Dim srch52 As String, found52 As Integer, i52 As Integer
For i52 = 26 To 26
    srch52 = "\\mb03a\mbxc01c\q\payor\ncbx1\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found52 = 0
    If Dir(srch52) <> "" Then   'is there such a file?
        Do
             found52 = found52 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i52) = found52
Next i52

Dim srch53 As String, found53 As Integer, i53 As Integer
For i53 = 28 To 28
    srch53 = "\\mb05a\ftproot\dts\bcbs_tn\inbound\" & "ubtnt001_???????_FAC_" & Format(Range("B2"), "yyyymmdd") & "_*_835.edi"
    found53 = 0
    If Dir(srch53) <> "" Then   'is there such a file?
        Do
             found53 = found53 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i53) = found53
Next i53

Dim srch54 As String, found54 As Integer, i54 As Integer
For i54 = 28 To 28
    srch54 = "\\mb05a\ftproot\dts\bcbs_tn\inbound\history\" & "ubtnt001_???????_FAC_" & Format(Range("B2"), "yyyymmdd") & "_*_835.edi"
    found54 = 0
    If Dir(srch54) <> "" Then   'is there such a file?
        Do
             found54 = found54 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i54) = found54
Next i54

Dim srch55 As String, found55 As Integer, i55 As Integer
For i55 = 28 To 28
    srch55 = "\\mb03a\mbxc01c\q\payor\tnbx1\in\wf\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found55 = 0
    If Dir(srch55) <> "" Then   'is there such a file?
        Do
             found55 = found55 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i55) = found55
Next i55

Dim srch56 As String, found56 As Integer, i56 As Integer
For i56 = 28 To 28
    srch56 = "\\mb03a\mbxc01c\q\payor\tnbx1\in\wf\history\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found56 = 0
    If Dir(srch56) <> "" Then   'is there such a file?
        Do
             found56 = found56 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i56) = found56
Next i56

Dim srch57 As String, found57 As Integer, i57 As Integer
For i57 = 30 To 30
    srch57 = "\\mb05a\ftproot\dts\blueshield_ca\inbound\" & "835_HEALTHLOGIC_*" & Format(Range("B2"), "yyyymmdd") & "*.835"
    found57 = 0
    If Dir(srch57) <> "" Then   'is there such a file?
        Do
             found57 = found57 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i57) = found57
Next i57

Dim srch58 As String, found58 As Integer, i58 As Integer
For i58 = 30 To 30
    srch58 = "\\mb05a\ftproot\dts\blueshield_ca\inbound\history\history\" & "835_HEALTHLOGIC_*" & Format(Range("B2"), "yyyymmdd") & "*.835"
    found58 = 0
    If Dir(srch58) <> "" Then   'is there such a file?
        Do
             found58 = found58 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i58) = found58
Next i58

Dim srch59 As String, found59 As Integer, i59 As Integer
For i59 = 30 To 30
    srch59 = "\\mb03a\mbxc01c\q\payor\cabs1\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found59 = 0
    If Dir(srch59) <> "" Then   'is there such a file?
        Do
             found59 = found59 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i59) = found59
Next i59

Dim srch60 As String, found60 As Integer, i60 As Integer
For i60 = 30 To 30
    srch60 = "\\mb03a\mbxc01c\q\payor\cabs1\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found60 = 0
    If Dir(srch60) <> "" Then   'is there such a file?
        Do
             found60 = found60 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i60) = found60
Next i60

Dim srch61 As String, found61 As Integer, i61 As Integer
For i61 = 32 To 32
    srch61 = "\\mb05a\ftproot\dts\echo\inbound\" & "ANSI835_*.DAT"
    found61 = 0
    If Dir(srch61) <> "" Then   'is there such a file?
        Do
             found61 = found61 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i61) = found61
Next i61

Dim srch62 As String, found62 As Integer, i62 As Integer
For i62 = 32 To 32
    srch62 = "\\mb05a\ftproot\dts\echo\inbound\history\" & "ANSI835_*.DAT"
    found62 = 0
    If Dir(srch62) <> "" Then   'is there such a file?
        Do
             found62 = found62 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i62) = found62
Next i62

Dim srch63 As String, found63 As Integer, i63 As Integer
For i63 = 32 To 32
    srch63 = "\\mb03a\mbxc01c\q\payor\echo1\in\wf\" & "?????K36.ARA"
    found63 = 0
    If Dir(srch63) <> "" Then   'is there such a file?
        Do
             found63 = found63 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i63) = found63
Next i63

Dim srch64 As String, found64 As Integer, i64 As Integer
For i64 = 32 To 32
    srch64 = "\\mb03a\mbxc01c\q\payor\echo1\in\wf\history\" & "?????K36.ARA"
    found64 = 0
    If Dir(srch64) <> "" Then   'is there such a file?
        Do
             found64 = found64 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i64) = found64
Next i64

Dim srch65 As String, found65 As Integer, i65 As Integer
For i65 = 36 To 36
    srch65 = "\\mb05a\ftproot\dts\emdeon\medical\inbound\" & "ERAPRD.DATA.13" & Format(Range("B2"), "mmdd") & "*"
    found65 = 0
    If Dir(srch65) <> "" Then   'is there such a file?
        Do
             found65 = found65 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i65) = found65
Next i65

Dim srch66 As String, found66 As Integer, i66 As Integer
For i66 = 36 To 36
    srch66 = "\\mb05a\ftproot\dts\emdeon\medical\inbound\history\history\" & "ERAPRD.DATA.13" & Format(Range("B2"), "mmdd") & "*"
    found66 = 0
    If Dir(srch66) <> "" Then   'is there such a file?
        Do
             found66 = found66 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i66) = found66
Next i66

Dim srch67 As String, found67 As Integer, i67 As Integer
For i67 = 36 To 36
    srch67 = "\\mb03a\mbxc01c\q\payor\emdeon\in\wf\" & "*.ARA"
    found67 = 0
    If Dir(srch67) <> "" Then   'is there such a file?
        Do
             found67 = found67 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i67) = found67
Next i67

Dim srch68 As String, found68 As Integer, i68 As Integer
For i68 = 36 To 36
    srch68 = "\\mb03a\mbxc01c\q\payor\emdeon\in\wf\history\" & "*.ARA"
    found68 = 0
    If Dir(srch68) <> "" Then   'is there such a file?
        Do
             found68 = found68 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i68) = found68
Next i68

Dim srch69 As String, found69 As Integer, i69 As Integer
For i69 = 38 To 38
    srch69 = "\\mb05a\ftproot\dts\mahealth\inbound\" & "S835_?????_426004813.edi"
    found69 = 0
    If Dir(srch69) <> "" Then   'is there such a file?
        Do
             found69 = found69 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i69) = found69
Next i69

Dim srch70 As String, found70 As Integer, i70 As Integer
For i70 = 38 To 38
    srch70 = "\\mb05a\ftproot\dts\mahealth\inbound\history\" & "S835_?????_426004813.edi"
    found70 = 0
    If Dir(srch70) <> "" Then   'is there such a file?
        Do
             found70 = found70 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i70) = found70
Next i70

Dim srch71 As String, found71 As Integer, i71 As Integer
For i71 = 38 To 38
    srch71 = "\\mb03a\mbxc01c\q\payor\mahc1\in\wf\" & Format(Range("B2"), "mm??") & "_???.ARA"
    found71 = 0
    If Dir(srch71) <> "" Then   'is there such a file?
        Do
             found71 = found71 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i71) = found71
Next i71

Dim srch72 As String, found72 As Integer, i72 As Integer
For i72 = 38 To 38
    srch72 = "\\mb03a\mbxc01c\q\payor\mahc1\in\wf\history\" & Format(Range("B2"), "mm??") & "_???.ARA"
    found72 = 0
    If Dir(srch72) <> "" Then   'is there such a file?
        Do
             found72 = found72 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i72) = found72
Next i72

Dim srch73 As String, found73 As Integer, i73 As Integer
For i73 = 40 To 40
    srch73 = "\\mb05a\ftproot\dts\medicaid_az\inbound\" & "AZ?835-02????-0?-13????-??????.TXT"
    found73 = 0
    If Dir(srch73) <> "" Then   'is there such a file?
        Do
             found73 = found73 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i73) = found73
Next i73

Dim srch74 As String, found74 As Integer, i74 As Integer
For i74 = 40 To 40
    srch74 = "\\mb05a\ftproot\dts\medicaid_az\inbound\history\" & "AZ?835-02????-0?-13????-??????.TXT"
    found74 = 0
    If Dir(srch74) <> "" Then   'is there such a file?
        Do
             found74 = found74 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i74) = found74
Next i74

Dim srch75 As String, found75 As Integer, i75 As Integer
For i75 = 40 To 40
    srch75 = "\\mb03a\mbxc01c\q\payor\azmd1\in\wf\" & "13???????.ARA"
    found75 = 0
    If Dir(srch75) <> "" Then   'is there such a file?
        Do
             found75 = found75 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i75) = found75
Next i75

Dim srch76 As String, found76 As Integer, i76 As Integer
For i76 = 40 To 40
    srch76 = "\\mb03a\mbxc01c\q\payor\azmd1\in\wf\history\" & "13???????.ARA"
    found76 = 0
    If Dir(srch76) <> "" Then   'is there such a file?
        Do
             found76 = found76 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i76) = found76
Next i76

Dim srch77 As String, found77 As Integer, i77 As Integer
For i77 = 42 To 42
    srch77 = "\\mb05a\ftproot\dts\medicaid_ga\inbound\" & "*_100589_8355010X12BATCH_ERA835_*_001_" & Format(Range("B2"), "yyyymmdd") & ".out.dat"
    found77 = 0
    If Dir(srch77) <> "" Then   'is there such a file?
        Do
             found77 = found77 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i77) = found77
Next i77

Dim srch78 As String, found78 As Integer, i78 As Integer
For i78 = 42 To 42
    srch78 = "\\mb05a\ftproot\dts\medicaid_ga\inbound\history\" & "*_100589_8355010X12BATCH_ERA835_*_001_" & Format(Range("B2"), "yyyymmdd") & ".out.dat"
    found78 = 0
    If Dir(srch78) <> "" Then   'is there such a file?
        Do
             found78 = found78 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i78) = found78
Next i78

Dim srch79 As String, found79 As Integer, i79 As Integer
For i79 = 42 To 42
    srch79 = "\\mb03a\mbxc01c\q\payor\gamd1\in\wf\" & "13" & Format(Range("B2"), "mmdd") & "??.ARA"
    found79 = 0
    If Dir(srch79) <> "" Then   'is there such a file?
        Do
             found79 = found79 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i79) = found79
Next i79

Dim srch80 As String, found80 As Integer, i80 As Integer
For i80 = 42 To 42
    srch80 = "\\mb03a\mbxc01c\q\payor\gamd1\in\wf\history\" & "13" & Format(Range("B2"), "mmdd") & "??.ARA"
    found80 = 0
    If Dir(srch80) <> "" Then   'is there such a file?
        Do
             found80 = found80 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i80) = found80
Next i80

Dim srch81 As String, found81 As Integer, i81 As Integer
For i81 = 46 To 46
    srch81 = "\\mb05a\ftproot\dts\molina\inbound\" & "HL_*CTX*_" & Format(Range("B2"), "yyyymmdd") & "_??????_MOL.835"
    found81 = 0
    If Dir(srch81) <> "" Then   'is there such a file?
        Do
             found81 = found81 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i81) = found81
Next i81

Dim srch82 As String, found82 As Integer, i82 As Integer
For i82 = 46 To 46
    srch82 = "\\mb05a\ftproot\dts\molina\inbound\history\" & "HL_*CTX*_" & Format(Range("B2"), "yyyymmdd") & "_??????_MOL.835"
    found82 = 0
    If Dir(srch82) <> "" Then   'is there such a file?
        Do
             found82 = found82 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i82) = found82
Next i82

Dim srch83 As String, found83 As Integer, i83 As Integer
For i83 = 46 To 46
    srch83 = "\\mb03a\mbxc01c\q\payor\mohc1\in\wf\" & "13" & Format(Range("B2"), "mmdd") & "?.ARA"
    found83 = 0
    If Dir(srch83) <> "" Then   'is there such a file?
        Do
             found83 = found83 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i83) = found83
Next i83

Dim srch84 As String, found84 As Integer, i84 As Integer
For i84 = 46 To 46
    srch84 = "\\mb03a\mbxc01c\q\payor\mohc1\in\wf\history\" & "13" & Format(Range("B2"), "mmdd") & "?.ARA"
    found84 = 0
    If Dir(srch84) <> "" Then   'is there such a file?
        Do
             found84 = found84 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i84) = found84
Next i84

Dim srch85 As String, found85 As Integer, i85 As Integer
For i85 = 48 To 48
    srch85 = "\\mb05a\ftproot\dts\rightcare\inbound\" & "R1?*.835"
    found85 = 0
    If Dir(srch85) <> "" Then   'is there such a file?
        Do
             found85 = found85 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i85) = found85
Next i85

Dim srch86 As String, found86 As Integer, i86 As Integer
For i86 = 48 To 48
    srch86 = "\\mb05a\ftproot\dts\rightcare\inbound\history\" & "R1?*.835"
    found86 = 0
    If Dir(srch86) <> "" Then   'is there such a file?
        Do
             found86 = found86 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i86) = found86
Next i86

Dim srch87 As String, found87 As Integer, i87 As Integer
For i87 = 48 To 48
    srch87 = "\\mb03a\mbxc01c\q\payor\rcmd1\in\wf\" & "R1?*.ARA"
    found87 = 0
    If Dir(srch87) <> "" Then   'is there such a file?
        Do
             found87 = found87 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i87) = found87
Next i87

Dim srch88 As String, found88 As Integer, i88 As Integer
For i88 = 48 To 48
    srch88 = "\\mb03a\mbxc01c\q\payor\rcmd1\in\wf\history\" & "R1?*.ARA"
    found88 = 0
    If Dir(srch88) <> "" Then   'is there such a file?
        Do
             found88 = found88 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i88) = found88
Next i88

Dim srch89 As String, found89 As Integer, i89 As Integer
For i89 = 52 To 52
    srch89 = "\\mb05a\ftproot\dts\tricare_pgba\inbound\" & "X*.X12.TRICARE"
    found89 = 0
    If Dir(srch89) <> "" Then   'is there such a file?
        Do
             found89 = found89 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i89) = found89
Next i89

Dim srch90 As String, found90 As Integer, i90 As Integer
For i90 = 52 To 52
    srch90 = "\\mb05a\ftproot\dts\tricare_pgba\inbound\history\" & "X*.X12.TRICARE"
    found90 = 0
    If Dir(srch90) <> "" Then   'is there such a file?
        Do
             found90 = found90 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i90) = found90
Next i90

Dim srch91 As String, found91 As Integer, i91 As Integer
For i91 = 52 To 52
    srch91 = "\\mb03a\mbxc01c\q\payor\tcar1\in\wf\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found91 = 0
    If Dir(srch91) <> "" Then   'is there such a file?
        Do
             found91 = found91 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i91) = found91
Next i91

Dim srch92 As String, found92 As Integer, i92 As Integer
For i92 = 52 To 52
    srch92 = "\\mb03a\mbxc01c\q\payor\tcar1\in\wf\history\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found92 = 0
    If Dir(srch92) <> "" Then   'is there such a file?
        Do
             found92 = found92 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i92) = found92
Next i92

Dim srch93 As String, found93 As Integer, i93 As Integer
For i93 = 54 To 54
    srch93 = "\\mb05a\ftproot\dts\tricare_west\inbound\" & "95018." & Format(Range("B2"), "yyyy") & "????.??????.PTDE.5010-835"
    found93 = 0
    If Dir(srch93) <> "" Then   'is there such a file?
        Do
             found93 = found93 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i93) = found93
Next i93

Dim srch94 As String, found94 As Integer, i94 As Integer
For i94 = 54 To 54
    srch94 = "\\mb05a\ftproot\dts\tricare_west\inbound\history\" & "95018." & Format(Range("B2"), "yyyy") & "????.??????.PTDE.5010-835"
    found94 = 0
    If Dir(srch94) <> "" Then   'is there such a file?
        Do
             found94 = found94 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i94) = found94
Next i94

Dim srch95 As String, found95 As Integer, i95 As Integer
For i95 = 54 To 54
    srch95 = "\\mb03a\mbxc01c\q\payor\tcar2\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found95 = 0
    If Dir(srch95) <> "" Then   'is there such a file?
        Do
             found95 = found95 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i95) = found95
Next i95

Dim srch96 As String, found96 As Integer, i96 As Integer
For i96 = 54 To 54
    srch96 = "\\mb03a\mbxc01c\q\payor\tcar2\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found96 = 0
    If Dir(srch96) <> "" Then   'is there such a file?
        Do
             found96 = found96 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i96) = found96
Next i96

Dim srch97 As String, found97 As Integer, i97 As Integer
For i97 = 56 To 56
    srch97 = "\\mb05a\ftproot\dts\uhc\inbound\" & "N_835_*_BPR*_" & Format(Range("B2"), "mmddyyyy") & "??????.RES"
    found97 = 0
    If Dir(srch97) <> "" Then   'is there such a file?
        Do
             found97 = found97 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i97) = found97
Next i97

Dim srch98 As String, found98 As Integer, i98 As Integer
For i98 = 56 To 56
    srch98 = "\\mb05a\ftproot\dts\uhc\inbound\history\" & "N_835_*_BPR*_" & Format(Range("B2"), "mmddyyyy") & "??????.RES"
    found98 = 0
    If Dir(srch98) <> "" Then   'is there such a file?
        Do
             found98 = found98 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i98) = found98
Next i98

Dim srch99 As String, found99 As Integer, i99 As Integer
For i99 = 56 To 56
    srch99 = "\\mb03a\mbxc01c\q\payor\cmun1\in\wf\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found99 = 0
    If Dir(srch99) <> "" Then   'is there such a file?
        Do
             found99 = found99 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i99) = found99
Next i99

Dim srch100 As String, found100 As Integer, i100 As Integer
For i100 = 56 To 56
    srch100 = "\\mb03a\mbxc01c\q\payor\cmun1\in\wf\history\" & Format(Range("B1"), "mmdd") & "_???.ARA"
    found100 = 0
    If Dir(srch100) <> "" Then   'is there such a file?
        Do
             found100 = found100 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i100) = found100
Next i100

Dim srch101 As String, found101 As Integer, i101 As Integer
For i101 = 13 To 13
    srch101 = "\\mb03a\mbxc01c\q\payor\gabx1\in\wf\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found101 = 0
    If Dir(srch101) <> "" Then   'is there such a file?
        Do
             found101 = found101 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i101) = found101
Next i101

Dim srch102 As String, found102 As Integer, i102 As Integer
For i102 = 13 To 13
    srch102 = "\\mb03a\mbxc01c\q\payor\gabx1\in\wf\history\" & Format(Range("B2"), "mmdd") & "_???.ARA"
    found102 = 0
    If Dir(srch102) <> "" Then   'is there such a file?
        Do
             found102 = found102 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i102) = found102
Next i102

End Sub

 

[xenou]

All those file addressses in your code should be in the worksheet cells for much easier visibility and maintenance. Then you search for the file and put the result in the same row. You probably need a cell for the date formatting since it's different for different files. And you have the added folder paths at the top to be concatenated with the rest of the file path. Then the code is reduced to ten lines or so. I can't help because the project is too much to rewrite all of this for you. I'll send a message to any other MVP's to see if anyone knows how to check a file date with DIR() (if it is possible). I don't use DIR enough so I'm not sure but at least that way we can patch up what you have enough so that it works.

 

[xenou]

Okay, I don't see any options for DIR to check file dates so scrap that. There is probably a way you could write a dos shell script to do if you really wanted to use dir alone. What I've done is use the FileSystemObject (as suggested in my first post). It's a rather major modification and you'll have to change all your blocks (which is also why I suggest you put the changing data in the worksheet so that you don't have to modify line after line of code, but rather just read from the worksheet).

I'm afraid I really don't think you have the skill to modify your code correctly, but I hope I'm wrong. You may be better off hiring a vba consultant to fix this for you properly (and perhaps re-tool it to be much easier to maintain). That said, here's my sample for one block of code, which would need to be repeated for all of them (be careful to update the row numbers and range columns as you go).

There remains a question about how to check for files from today. File dates can be tricky. I usually check the Last Modified date rather than the created date. It's not clear (since you have dates in the file name) why you need to check the dates anyway - aren't the dates in the file names good enough? Or maybe not every file is named that way. Anyway, there's also a problem with Sundays and holidays - will you sometimes need to check for files from the day before yesterday, or two days ago? I don't think this code is right yet so *be careful* and subject to testing and probing for potential gotchas.

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

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

[COLOR="Navy"]Dim[/COLOR] srch [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] cnt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] VBA.Collection
[COLOR="Navy"]Dim[/COLOR] RowNum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

    [COLOR="SeaGreen"]'//Create objects[/COLOR]
    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]Set[/COLOR] col = [COLOR="Navy"]New[/COLOR] VBA.Collection


    [COLOR="SeaGreen"]'-----------------------------------------------------------------------------[/COLOR]
    [COLOR="SeaGreen"]'//Search String[/COLOR]
    srch = "\\root\inbound\" & "*.835"
    RowNum = 4    [COLOR="SeaGreen"]'The row number for this file on the worksheet (to save count in)[/COLOR]
    
    [COLOR="SeaGreen"]'//Search for first file[/COLOR]
    s = Dir(srch)
    [COLOR="Navy"]If[/COLOR] s <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="SeaGreen"]'//File exists - add to collection and keep searching[/COLOR]
        col.Add s
        [COLOR="Navy"]Do[/COLOR]
            s = Dir(srch)
            [COLOR="Navy"]If[/COLOR] s <> "" [COLOR="Navy"]Then[/COLOR]
                col.Add s
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]Loop[/COLOR] [COLOR="Navy"]While[/COLOR] s <> ""
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

    [COLOR="SeaGreen"]'//Check file dates[/COLOR]
    [COLOR="Navy"]If[/COLOR] col.Count > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] col.Count
            [COLOR="Navy"]If[/COLOR] FSO.GetFile(col(i)).DateLastModified >= [COLOR="Navy"]Date[/COLOR] _
                [COLOR="Navy"]And[/COLOR] FSO.GetFile(col(i)).DateLastModified < (Date + 1) [COLOR="Navy"]Then[/COLOR]
                cnt = cnt + 1
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]Next[/COLOR] i
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

    [COLOR="SeaGreen"]'//Write result to worksheet[/COLOR]
    Sheets("DTS Xmissions").Range("C" & RowNum) = cnt

    [COLOR="SeaGreen"]'//Reset collection and count variables[/COLOR]
    [COLOR="Navy"]Set[/COLOR] col = [COLOR="Navy"]New[/COLOR] VBA.Collection
    cnt = 0
    [COLOR="SeaGreen"]'-----------------------------------------------------------------------------[/COLOR]



    [COLOR="SeaGreen"]'//repeat...[/COLOR]


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

 

[jujubeans69]

Thank you! I will give this a try. Yes, I just needed clarification on where to specify row and column output using your code. Not all of the files contain a date format, and not all of them are for the current (today's) date, so that will not work.

 

[Fazza]

Please refer to FileDateTime in VBA help. Below is one way to use it with Dir

Sub example_usage()

  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.*", DateToCheck:=Date - 1) & " file/s today"
  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.*", DateToCheck:=Date - 1) & " file/s yesterday"
  
  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.*", DateToCheck:=DateSerial(Year:=2013, Month:=10, Day:=1)) & " file/s 1 October 2013 "
  
  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.xl*", DateToCheck:=Date) & " Excel file/s today"

End Sub

Function FileCountByDate(ByVal FilePathToCheck As String, ByVal FilePatternToCheck As String, ByVal DateToCheck As Date) As Long

  Dim strFileName As String
  Dim strDateToMatch As String

  FileCountByDate = 0
  strDateToMatch = Format$(DateToCheck, "yyyymmdd")

  strFileName = Dir(FilePathToCheck & Application.PathSeparator & FilePatternToCheck)
  Do While Len(strFileName) > 0
    If Format$(FileDateTime(FilePathToCheck & Application.PathSeparator & strFileName), "yyyymmdd") = strDateToMatch Then FileCountByDate = FileCountByDate + 1
    strFileName = Dir
  Loop

End Function

 

[Fazza]

Checking for only one date seemed a bit limiting. A slight modification allows more general usage. regards

Private Enum DateCheckType
  dcOnThisDate
  dcOnOrBeforeThisDate
  dcOnOrAfterThisDate
  dcOnOrBetweenTheseDates
  dcOutsideTheseDates
End Enum


Private Function FileCountByDate(ByVal FilePathToCheck As String, ByVal FilePatternToCheck As String, ByVal MyCheckType As DateCheckType, ByVal DateToCheck As Date, Optional ByVal LaterDateToCheck As Date) As Long


  Dim strFileName As String
  Dim lngDateToCheck As Long
  Dim lngLaterDateToCheck As Long


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


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


End Function


Sub test()


  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.*", MyCheckType:=dcOnThisDate, DateToCheck:=Date) & " file/s today"
  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.*", MyCheckType:=dcOnThisDate, DateToCheck:=Date - 1) & " file/s yesterday"
  
  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.*", MyCheckType:=dcOnOrAfterThisDate, DateToCheck:=Date - 2) & " file/s 2 days or newer"
    
  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.*", MyCheckType:=dcOnOrBetweenTheseDates, _
    DateToCheck:=DateSerial(Year:=2013, Month:=10, Day:=1), LaterDateToCheck:=Date - 1) & " file/s from 1 October 2013 to yesterday"
  
  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.xl*", MyCheckType:=dcOnOrBeforeThisDate, DateToCheck:=Date) & " Excel file/s today or earlier"
  Debug.Print FileCountByDate(FilePathToCheck:="C:", FilePatternToCheck:="*.xl*", MyCheckType:=dcOnOrBeforeThisDate, DateToCheck:=Date - 7) & " Excel file/s a week ago or earlier"
  
End Sub

 

[jujubeans69]

Okay, I don't see any options for DIR to check file dates so scrap that. There is probably a way you could write a dos shell script to do if you really wanted to use dir alone. What I've done is use the FileSystemObject (as suggested in my first post). It's a rather major modification and you'll have to change all your blocks (which is also why I suggest you put the changing data in the worksheet so that you don't have to modify line after line of code, but rather just read from the worksheet).

I'm afraid I really don't think you have the skill to modify your code correctly, but I hope I'm wrong. You may be better off hiring a vba consultant to fix this for you properly (and perhaps re-tool it to be much easier to maintain). That said, here's my sample for one block of code, which would need to be repeated for all of them (be careful to update the row numbers and range columns as you go).

There remains a question about how to check for files from today. File dates can be tricky. I usually check the Last Modified date rather than the created date. It's not clear (since you have dates in the file name) why you need to check the dates anyway - aren't the dates in the file names good enough? Or maybe not every file is named that way. Anyway, there's also a problem with Sundays and holidays - will you sometimes need to check for files from the day before yesterday, or two days ago? I don't think this code is right yet so *be careful* and subject to testing and probing for potential gotchas.

xenou, I tried the code on only 1 row (with 2 columns). A \inbound folder, and a \inbound\history folder. The \inbound folder was instant, as it had no files in there. The \inbound\history folder was taking forever. It only has 55 files in there. Do you have any idea why it would take 5-10 minutes just to determine the last modified date on 55 files in one folder? Thanks.