Sub ProcessFiles()
Dim nItems As Long

nItems = CountTests("C:\myNew\010212.xls", "Text") + _
CountTests("C:\myNew\010306.xls", "Text") + _
CountTests("C:\myNew\020309.xls", "Text") + _
CountTests("C:\myNew\030902.xls", "Text") + _
CountTests("C:\myNew\ABC.xls", "Text")

Workbooks.Add
ActiveSheet.Range("A1").Value = nItems
End Sub

Private Function CountTests(wbName As String, _
lookup_val As String) As Long
Dim oWS As Worksheet

Workbooks.Open wbName
For Each oWS In ActiveWorkbook.Worksheets
If oWS.Range("A1").Value = lookup_val Then
CountTests = 1
End If
Next
ActiveWorkbook.Close savechanges:=False

End Function


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vt7" wrote in message
oups.com...
Hi,
I am rather novice user of Excel VBA. My "macro" problem is:
1. Open 5 .xls files.
2. In every worksheet of every file check if Range("A1").Value is
"Text"
3. Close 5 .xls files.
4. Print the results of searching f.e. "I have found "Text" 56 times."
in new workbook.

Please help...
I'm looking forward to hearing from you :)