Vista - Vbscript to Search Excel

Hi,

I am trying to write a Vbscript which will search for some keywords in all
the worksheets of a excel file and if a match is found then the script should
write that row to a text file. I am able to find in which sheet the keywords
are in. But i am not able to find a way to write the value of that row to
text file. I am completely lost. Can anyone of you please help me in writing
the matched value to text file. Thanks in advance. Below is the code.

Const ForReading = 1
strSearchTerm = ["abc","pqrs","test"]
strPath = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "test.xls"
boolMatchCase = False


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
objExcel.DisplayAlerts = FALSE
objExcel.ScreenUpdating = False
Const xlFormulas = -4123
Const xlPart = 2
Const xlByRows = 1
Const xlNext = 1


Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True)
For Each objWorkSheet In objWorkBook.Sheets
intFoundRow = -1
objWorkSheet.Activate

Set objCell = objWorkSheet.Cells(1, "A")
Set objCell = objWorkSheet.Cells.Find(strSearchTerm, objCell, xlFormulas,
xlPart, xlByRows, xlNext, boolMatchCase)

If Not objCell Is Nothing Then

If objCell.Row > intFoundRow Then
strResults = strResults & VbCrLf & objWorkSheet.Name
intFoundRow = objCell.Row

Else
Set objCell = Nothing
End If

End If
Next
objWorkBook.Close
objExcel.ScreenUpdating = True
objExcel.Quit



If strResults <> "" Then
Wscript.echo strSearchTerm & "Keyword was found on the following sheets:" &
strResults
Else
Wscript.echo strSearchTerm & " Keyword was not found"
End If

"Codeblack" <Codeblack@xxxxxx> wrote in message
news:EC354DDB-D2E7-48C8-8779-13EE7AD86919@xxxxxxHave a look at the WriteLine method of the File System Object. You can see a
complete example in the downloadable help file script56.chm.

My suggestion....

Write it all in VBA and keep the VBA in the Excel Workbook.

Use VBScript to start up Excel and to start off the VBA macro.

Only code in VBScript when Excel can't do the job.

Il giorno Fri, 23 Jan 2009 18:34:34 -0800 (PST), "gimme_this_gimme_that@xxxxxx"
<gimme_this_gimme_that@xxxxxx> ha scritto:
filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
oExcel.Run "macro1"
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
Set oExcel = Nothing
--
Giovanni Cenati (Bergamo, Italy)
Write to "Reventlov" at katamail com
http://digilander.libero.it/Cenati (Esempi e programmi in VbScript)
--

Thanks for your suggestion. But i cannot use VBA in this case as the files in
which i search for keywords is not constant and there are hundreds of files
which in need to search. Any inputs please.

"Codeblack" <Codeblack@xxxxxx> wrote in message
news:9576CC20-9D38-4F87-9374-2C4F4BA01F3C@xxxxxxHere is the example from the help file I mentioned. Which part are you
having problems with?
Function WriteLineToFile
Const ForReading = 1, ForWriting = 2
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile("c:\testfile.txt", ForWriting, True)
f.WriteLine "Hello world!"
f.WriteLine "VBScript is fun!"
Set f = fso.OpenTextFile("c:\testfile.txt", ForReading)
WriteLineToFile = f.ReadAll
End Function

Thanks MVP. I know how to write the data to text file but i dont know how to
retrive/copy the data from the excel cell when a match is found.

"Codeblack" <Codeblack@xxxxxx> wrote in message
news:A42B1190-DAF5-4AA6-85ED-85849724896D@xxxxxxTry this modification to your code. Note that I dropped one line!
objWorkSheet.Activate
Set objCell = objWorkSheet.Cells.Find(strSearchTerm, objWorkSheet.Cells(1,
"A"), xlFormulas, xlPart, xlByRows, xlNext, boolMatchCase)
x = objCell.value

Codeblack wrote:IIUR all you have to do is to append the found Row number to strResults

strResults = strResults&VbCrLf&objWorkSheet.Name&" Row:"&objCell.Row

And you should write that variable to a file before exiting th script.

Oh, and use a simple searchstr for testing like strSearchTerm = "abc"

With this and a prepared test.xls I got:

Tabelle1 Row:3
Tabelle2 Row:4
Tabelle3 Row:6

--
HTH
Matthias

Thanks a lot for your timely help. this is working now. But the script is
finding only one occurence of the stirng in the excel sheet. If there are
multiple occurence of the string in the same sheet, then it is not showing
the details.

For example : if the search string "abc" is in Sheet1: Row1 and also in Row
45, the output is showing only the Row1 and not showing Row 45.

Do you have any clue why this is happening.