Prev: Help desperately needed.
Next: Connecting to Win32_DFSTarget on Windows Server 2008
From: Codeblack on 23 Jan 2009 09:21
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

From: Pegasus (MVP) on 23 Jan 2009 10:39

"Codeblack" <Codeblack(a)discussions.microsoft.com> wrote in message
news:EC354DDB-D2E7-48C8-8779-13EE7AD86919(a)microsoft.com...
> 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
>

Have a look at the WriteLine method of the File System Object. You can see a
complete example in the downloadable help file script56.chm.


From: gimme_this_gimme_that on 23 Jan 2009 21:34
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.



From: Reventlov on 25 Jan 2009 16:41
Il giorno Fri, 23 Jan 2009 18:34:34 -0800 (PST), "gimme_this_gimme_that(a)yahoo.com"
<gimme_this_gimme_that(a)yahoo.com> ha scritto:

>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.

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)
--
From: Codeblack on 27 Jan 2009 01:33
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.
 |  Next  |  Last
Pages: 1 2 3 4
Prev: Help desperately needed.
Next: Connecting to Win32_DFSTarget on Windows Server 2008