MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Code to search for a desired string in external independent excel

  Asked By: Kiral    Date: Dec 15    Category: MS Office    Views: 2104

I want to have a command button in an independent excel worksheet which
will execute a macro which in turn would search for a particular text
string such as "Error" within a selection of independent excel files
residing in a local directory such as C:\ and return a Pass/Fail value
when it finds such a string in the collection of excel files.

Command button Cmd_1 in "Sheet1" of an excel sheet (template) will fire
a macro to search for a text string such as "Error" in two independent
files (Data1.xls, Data2.xls) and return True/False depending upon
whether the string "Error" is found in either of them.

How do I come up with a code for the above? What search functions could
I use.?



3 Answers Found

Answer #1    Answered By: Wilbur Hall     Answered On: Dec 15

If it is a string  "Error" You could search  using the normal search tool.

You would need to use the FSO to find all the .xls files  in a directory

You would open each of them and then search for the string. The search will
return a value which can be interpreted as "found" or "Not Found" You would
use this result to set a flag which will indicate if the string was found.

Answer #2    Answered By: Alfonsine Miller     Answered On: Dec 15

By normal serach tool do you mean Instr functions  or look up tool
such as MATCH or LOOKUP?

Also using FSO, would I be able to look up into the concerned excel
files without having to import it into my application ie. excel  files
where the macro  resides?

Answer #3    Answered By: Fedde Bakker     Answered On: Dec 15

The search  part of the macro  would look like

Dim MyWorkbook As Workbook
Dim MySheet As Worksheet
[FSO Code to open each workbook in turn  here]

MyString = "error"
For Each MySheet In MyWorkbook
x = MySheet.UsedRange.Find(MyString).Activate
If Not x Is Nothing Then
MyFlag = 1
End If

The FSO will find the workbooks and open them. You will not be able to access
the contents of them without opening them in the application. (Unless you use
text  search facility completely outside VBA).