jujubeans69
New Member
- Joined
- Aug 17, 2012
- Messages
- 43
I have a current macro that is based on previously used macros. It is used for tracking incoming files that process downstream. The only major problem I have right now is trying to restrict the directory search to the current date (or the one enter into B4). Any help would be greatly appreciated! Thanks.
Not sure how to attach the spreadsheet, so I copied it and the script below:
[/URL][/IMG]
Not sure how to attach the spreadsheet, so I copied it and the script below:
Code:
Sub WellStar()
Dim srch1 As String, found1 As Integer, i1 As Integer
For i1 = 4 To 143
srch1 = "\\mb05a\ftproot\sites\wellstar\mb\wellstar2hlsc\remits\history\" & Range("B4" & i1) & "*"
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("WellStar").Range("G" & i1) = found1
Next i1
Dim srch2 As String, found2 As Integer, i2 As Integer
For i2 = 4 To 143
srch2 = "\\mb05a\ftproot\sites\wellstar\mb\wellstar2hlsc\remits\er\" & Range("B4" & i1) & "*"
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("WellStar").Range("H" & i2) = found2
Next i2
Dim srch3 As String, found3 As Integer, i3 As Integer
For i3 = 4 To 143
srch3 = "\\mb03a\mbxc01c\q\payor\" & Range("E4" & i3) & "\in\wf\" & Range("B4" & i3) & "*.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("WellStar").Range("I" & i3) = found3
Next i3
Dim srch4 As String, found4 As Integer, i4 As Integer
For i4 = 4 To 143
srch4 = "\\mb03a\mbxc01c\q\payor\" & Range("E4" & i4) & "\in\wf\history\" & Range("B4" & i4) & "*.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("WellStar").Range("J" & i4) = found4
Next i4
'Need a function >HERE< to do a text search on a file, but maybe not possible
Dim srch6 As String, found6 As Integer, i6 As Integer
For i6 = 4 To 143
srch6 = "\\" & Range("D4" & i6) & "\c-drive\combatch\" & Range("E" & i6) & "\835\" & "*." & Range("C" & i6)
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("WellStar").Range("L" & i6) = found6
Next i6
Dim srch7 As String, found7 As Integer, i7 As Integer
For i7 = 4 To 143
srch7 = "\\mb01a\mbp8xc\q\remits\wf\history\" & "?????" & Range("F" & i7) & "." & Range("C" & i7)
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("WellStar").Range("M" & i7) = found7
Next i7
Dim srch8 As String, found8 As Integer, i8 As Integer
For i8 = 4 To 143
srch8 = "\\mb01a\mbp8xc\q\remits\output\" & "?????" & Range("F" & i8) & "." & Range("C" & i8)
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("WellStar").Range("N" & i8) = found8
Next i8
Dim srch9 As String, found9 As Integer, i9 As Integer
For i9 = 4 To 143
srch9 = "\\mb01a\mbp8xc\q\remrpt\wf\history\" & "?????" & Range("F" & i9) & "." & Range("C" & i9)
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("WellStar").Range("O" & i9) = found9
Next i9
Dim srch10 As String, found10 As Integer, i10 As Integer
For i10 = 4 To 143
srch10 = "\\mb01a\mbp8xc\q\remrpt\output\" & "?????" & Range("F" & i10) & "." & Range("C" & i10)
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("WellStar").Range("P" & i10) = found10
Next i10
Dim srch11 As String, found11 As Integer, i11 As Integer
For i11 = 4 To 143
srch11 = "\\mb01a\mbp8xc\q\remarc\wf\history\" & "?????" & Range("F" & i11) & "." & Range("C" & i11)
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("WellStar").Range("Q" & i11) = found11
Next i11
Dim srch12 As String, found12 As Integer, i12 As Integer
For i12 = 4 To 143
srch12 = "\\mb01a\mbp8xc\q\remarc\output\" & "?????" & Range("F" & i12) & "." & Range("C" & i12)
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("WellStar").Range("R" & i12) = found12
Next i12
Dim srch13 As String, found13 As Integer, i13 As Integer
For i13 = 4 To 143
srch13 = "\\mb01a\mbp8xc\q\pnc\wf\history\" & "?????" & Range("F" & i13) & "." & Range("C" & i13)
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("WellStar").Range("S" & i13) = found13
Next i13
Dim srch14 As String, found14 As Integer, i14 As Integer
For i14 = 4 To 143
srch14 = "\\mb01a\mbp8xc\q\pnc\output\" & "?????" & Range("F" & i14) & "." & Range("C" & i14)
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("WellStar").Range("T" & i14) = found14
Next i14
End Sub
