Vista - Regular Expression Help Required

Hi,

I have written a Vbscript which will search for a specific pattern of text
in a text file and if there is match found then the script will write that
corresponding line to word file. Now i am trying to accomplish the same with
excel file as input instead of text file, but i am not able to do so. I think
i am doing some mistake in reading the excel file. Can anyone help me with
this. Below is my code. Any help is greatly appreciated.


Const ForReading = 1

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Pattern = "abc|tree"
objRegEx.IgnoreCase = True

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Open("Z:\RegEX\Test.xls")
intRow = 2

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
Set objSelection = objWord.Selection

Do Until objExcel.Cells(intRow, 1).Value = ""
strSearchString = objExcel.Cells(intRow, 1).Value
Set colMatches = objRegEx.Execute(strSearchString)
If colMatches.Count > 0 Then
For Each strMatch in colMatches
objSelection.TypeText " " & strSearchString & vbCRLF
Next
End If
Loop

Have you tried outputting what is found in each cell, just to make sure it's
retrieving the expected value?

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

"Codeblack" <Codeblack@xxxxxx> wrote in message
news:953BFCB1-F771-457C-A269-F26E92E45B70@xxxxxx

The other things are that you don't increment the intRows variable and you
only open the workbook, you never select a particular worksheet.

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

"Codeblack" <Codeblack@xxxxxx> wrote in message
news:953BFCB1-F771-457C-A269-F26E92E45B70@xxxxxx

Codeblack schrieb:Please specify exactly what goes wrong.
With some indentation it looks like there is a
... intRow = intRow + 1
missing here

"Codeblack" <Codeblack@xxxxxx> wrote in message
news:953BFCB1-F771-457C-A269-F26E92E45B70@xxxxxxAs Joe & ekkehard mentioned, it appears that your only problem is you
forgot to increment your 'intRow' variable. You might also consider changing
your loop from:

Do Until objExcel.Cells(intRow, 1).Value = ""
[...your code...]
Loop

To:

For intRow = 2 to objExcel.Range("A65536").End(-4162).Row
[...your code...]
Next

Even if you increment your variable, your code will stop at the first
blank cell. The above change should resolve this issue. If that was your
intention, please disregard this suggestion.

Thanks everyone for your prompt response. As per your suggestion, i made
changes to the script, but now the script dosen't throw any error neither it
writes the output to word file. Below is the updated code. Any inputs please.


Const ForReading = 1

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Pattern = "abc|tree"
objRegEx.IgnoreCase = True

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Open("Z:\RegEX\Test.xls")
intRow = 1

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
Set objSelection = objWord.Selection


For Each objWorkSheet In objWorkBook.Sheets
intRow = introw+1
objWorkSheet.Activate
Set objCell = objWorkSheet.Cells(1, "A")

strSearchString = objExcel.Cells(intRow, 1).Value
Set colMatches = objRegEx.Execute(strSearchString)
If colMatches.Count > 0 Then
For Each strMatch in colMatches
objSelection.TypeText " " & strSearchString & vbCRLF
Next
End If
Next

Can anyone suggest on this. I am completely lost. Any help on this will be
greatly appreciated.

Can anyone please help me with this.

"Codeblack" <Codeblack@xxxxxx> wrote in message
news:914CB3C4-D5DF-456F-A87E-9292DF9598E5@xxxxxxOK, first I noticed that you are enumerating your sheets, but not your
cells / rows. You need another loop. I have some questions. Are you wanting
to search only column 'A' in each worksheet in the workbook or search all
columns? If a match is found, do you want only the match returned or the
entire row? If the entire row is desired, do you want the data delimited? If
so, how? In either case, do you want the output to include the location
(sheet name / number & row number) the discovery was made at?

James,

Thanks a lot for looking into this. Please find my answers below.

I want to search all the columns in the sheet and if a match is found then
the script should write the contents of that cell to a word file. The output
should contain the Sheet Number, Row Number and the cell value where the
match is found.