Macro that searches 5 files .xls and in every worksheet in every file is looking for "text".
 

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 :)

Macro that searches 5 files .xls and in every worksheet in every file is looking for "text".
 

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 :)

Macro that searches 5 files .xls and in every worksheet in every file is looking for "text".
 

It works perfectly with a small correction:

' CountTests = 1
CountTests = Countests + 1 ,

Thank you very much for your help !!!!!

Macro that searches 5 files .xls and in every worksheet in every file is looking for "text".
 

Sorry for that type

--
HTH

Bob Phillips

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

"vt7" wrote in message
oups.com...

It works perfectly with a small correction:

' CountTests = 1
CountTests = Countests + 1 ,

Thank you very much for your help !!!!!

Macro that searches 5 files .xls and in every worksheet in every file is looking for "text".
 

Roedd <<Bob Phillips wedi ysgrifennu:

Sorry for that type


typo? ;-)

Rob

Macro that searches 5 files .xls and in every worksheet in every file is looking for "text".
 

Proves my point <bg

--
HTH

Bob Phillips

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

"Robert Bruce" <rob@analytical-dynamicsdotcodotukay wrote in message
...
Roedd <<Bob Phillips wedi ysgrifennu:

Sorry for that type


typo? ;-)

Rob