String Comparison Pattern Match to Stop at Each New Line

[JonXL]

Hello,

I have written up a series of procedures that look in each of all the text files within a given directory to find if any of the files contain a particular pattern of text.

The process I have works just fine. Where I am having issues is with finding my string within the text file. What I want to find is "PUSH:" followed by zero or any number of characters then either "Q" or "C" and again followed by any number of characters. I want it to do this without searching across new lines (in the text file those are CRLF). The code I have now for the comparison doesn't work because on lines after "PUSH:" in the file there are words with "Q" and/or "C" and these are being picked up as a match (strTextFile represents the entire text of a particular text file):

If strTextFile Like "*PUSH*[QC]*" Then

That would match, for example:

BLAH BLAH PUSH: UJ
CONTINUE LINE

... when I really only want it to match things like these:

BLAH BLAH PUSH: JCO
CONTINUE LINE

BLAH BLAH PUSH: Q
CONTINUE LINE

BLAH BLAH PUSH: IOCQ PULL: OP
CONTINUE LINE

And not these:

BLAH BLAH PUSH: JP
CONTINUE LINE

BLAH BLAH PUSH: UI
QUIT

Maybe I need a RegEx? Not really sure. I can loop through the text files one line at a time in which case the code I have above would work fine, but that would be slower so I am trying to do the search on the entire contents of the file in one go.

Any help is appreciated.

Thank you!
Jon

 

[6StringJazzer]

I would use RegEx but I would really suggest using an off-the-shelf tool to do this. You seem to be using Excel VBA to write a custom solution that is not related to Excel. There are tools that specialize in this. I use FileLocator Lite to do this kind of thing. It's free and it can do RegEx searches on either content or file names (or both). It is much better than Windows built-in searches and it's fast.

If you want to stick with Excel, use RegEx with the pattern "^.*PUSH.*[QC].*$"

Are you just trying to determine if the file has it, or capture the string and do something with it?

 

[JonXL]

I would use RegEx but I would really suggest using an off-the-shelf tool to do this. You seem to be using Excel VBA to write a custom solution that is not related to Excel. There are tools that specialize in this. I use FileLocator Lite to do this kind of thing. It's free and it can do RegEx searches on either content or file names (or both). It is much better than Windows built-in searches and it's fast.

Thank you for the suggestion. Downloading a special file for this isn't something I can do. And this isn't something I would do often, so I just went with something I already had. As to this not having anything to do with Excel, that's true and I even wondered about putting this in a different forum but since I wrote the script in Excel I put it here.

If you want to stick with Excel, use RegEx with the pattern "^.*PUSH.*[QC].*$"

Thank you. I will give that a try. Can you tell me how I would use that? My string for the whole text file is strTextFile.

Are you just trying to determine if the file has it, or capture the string and do something with it?

If I could have it return the text that matched the pattern, that would be extra helpful but at a minimum it needs to just be T/F whether it finds the match.

 

[6StringJazzer]

Here is a general-purpose function. I have not tested this with the exact situation you are describing. I believe ^ should match the beginning of a line and $ the end, rather than the beginning and end of the entire string, but I could be mistaken. If so then we need to add characters to look for line breaks.

If RegExpTest(strTextFile, "^.*PUSH.*[QC].*$") Then
' The rest of your logic here
End If

' Look for pattern FindWhat in string FindIn. Return TRUE if found, otherwise FALSE
Function RegExpTest(FindIn As String, _
                    FindWhat As String, _
                    Optional IgnoreCase As Boolean = False) As Boolean
   
    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")
   
    RE.Pattern = FindWhat
    RE.IgnoreCase = IgnoreCase
    RegExpTest = RE.Test(FindIn)
   
End Function

 

[JonXL]

The expression I ended up with was \bPUSH.*[QC].*\b and I used the 'matching and displaying a pattern' function from here:

VBA Regex

This tutorial will demonstrate how to use Regex in VBA. What is Regex? Regex stands for regular expression. A regular expression is a pattern made up of a

www.automateexcel.com

This worked out well because, while I wasn't originally going to worry about the return text, I was able to rework that function to list each match and its file on a sheet which I then used to validate the data precisely from my other system.

Thank you for the help. I think I am now a little closer to understanding RegEx!