Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 !!!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 !!!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
VLOOKUP gives duplicate results for "empty" searches | Excel Worksheet Functions | |||
.csv drops "0" when saved, even if file was set as "text". | Excel Discussion (Misc queries) | |||
Sending macro based e-mail with built-in "Heading" and "Text" | Excel Programming | |||
How to use macro to rename worksheet with date +"text" | Excel Programming |