check mutiple files with wildcard name and tally to differnt xls f
 

I have close to 500 files that that I would like to search. First I would
like to find all the files that have "2006" in the file name. Then, open each
file, check cell E5 on sheet 1 of that file for a true or false value. If the
vakue is true, I want to increase the value of cell B1 (on the .xls sheet
that the macro ran from, not the found file) by one. When the macro is
complete, I would like cell B1 to show a total of all E% True values for all
..xls files with 2006 in the filename. I also want this sheet to be the only
one still open (all *2006*.xls files closed).

Thank you in advance for any help here.

check mutiple files with wildcard name and tally to differnt xls f
 

Sub CheckFiles()
Dim cntTrue As Long, cnt As Long
Dim rng As Range, bk As Workbook
Dim fName As String
Dim sPath As String, s As String
Set rng = ActiveSheet.Range("B1")
rng.Value = 0
fName = Dir("C:\MyFolder\*2006*.xls")
Do While fName < ""
Set bk = Workbooks.Open("C:\Myfolder\" & fName)
cnt = cnt + 1
If bk.Worksheets(1).Range("E5").Value = True Then
cntTrue = cntTrue + 1
rng = cntTrue
End If
bk.Close SaveChanges:=False
fName = Dir()
Loop
s = "Files Checked: " & cnt & Chr(10) & "Files True: " & cntTrue & _
Chr(10) & "Percent True: " & Format(cntTrue / cnt, "00%")
rng.Value = s
End Sub



--
Regards,
Tom Ogilvy


"JCanyoneer" wrote in message
...
I have close to 500 files that that I would like to search. First I would
like to find all the files that have "2006" in the file name. Then, open
each
file, check cell E5 on sheet 1 of that file for a true or false value. If
the
vakue is true, I want to increase the value of cell B1 (on the .xls sheet
that the macro ran from, not the found file) by one. When the macro is
complete, I would like cell B1 to show a total of all E% True values for
all
.xls files with 2006 in the filename. I also want this sheet to be the
only
one still open (all *2006*.xls files closed).

Thank you in advance for any help here.

check mutiple files with wildcard name and tally to differnt x
 

That is perfect!!! Thank you so much! You are a stud!

"Tom Ogilvy" wrote:

Sub CheckFiles()
Dim cntTrue As Long, cnt As Long
Dim rng As Range, bk As Workbook
Dim fName As String
Dim sPath As String, s As String
Set rng = ActiveSheet.Range("B1")
rng.Value = 0
fName = Dir("C:\MyFolder\*2006*.xls")
Do While fName < ""
Set bk = Workbooks.Open("C:\Myfolder\" & fName)
cnt = cnt + 1
If bk.Worksheets(1).Range("E5").Value = True Then
cntTrue = cntTrue + 1
rng = cntTrue
End If
bk.Close SaveChanges:=False
fName = Dir()
Loop
s = "Files Checked: " & cnt & Chr(10) & "Files True: " & cntTrue & _
Chr(10) & "Percent True: " & Format(cntTrue / cnt, "00%")
rng.Value = s
End Sub



--
Regards,
Tom Ogilvy


"JCanyoneer" wrote in message
...
I have close to 500 files that that I would like to search. First I would
like to find all the files that have "2006" in the file name. Then, open
each
file, check cell E5 on sheet 1 of that file for a true or false value. If
the
vakue is true, I want to increase the value of cell B1 (on the .xls sheet
that the macro ran from, not the found file) by one. When the macro is
complete, I would like cell B1 to show a total of all E% True values for
all
.xls files with 2006 in the filename. I also want this sheet to be the
only
one still open (all *2006*.xls files closed).

Thank you in advance for any help here.