How to use regexp and arrays to match groups or sets of 3 individual patterns?

[jbaich]

Sorry if this get's duplicated, tried to post, but wasn't logged in and then auto redirect didn't go anywhere, so went back and trying again...

Hey all, I'm new to Reg exp and I'm going to try to use it in Outlook... I know this is an Excel forum, but there are so many super smart and helpful people here I figured it might be worth asking.

I'm trying to match patterns, which occur in sets of 3. I have posted this question on another forum as well, where i was originally introduced to the Reg Exp function https://forums.slipstick.com/threads/95172-is-this-possible-to-do-with-a-macro/#post-347604, but with the holiday season, i'm not sure if I should be expecting a response in the short term and the usefulness of this macro (if I can get it to work) is increasing exponentially by the day, so I'd really love to figure something out in a hurry

So what I'm thinking is as follows, please correct me if I'm wrong:

example:
Pattern 1 = date
Pattern 2 = time
pattern 3 = postal code

The 3 individual patterns are SubMatches and together they would be a MatchCollection... right? Except instead of finding all the pattern 1 collections, all the pattern 2 collections and all the pattern 3 collections, I need to find all the pattern (1,2,3) collections... and store them for use later so i also need to create variables for them as they are identified... ie: collection 1, collection 2, collection 3... etc.

So I'm trying to figure out how to loop through and find all of the MatchCollections (1,2,3,) in my document... not sure how many there may be. I was thinking I could run a count of all the pattern 1 matches, which should give me the number of collections I'll need and then do an if loop for i = 1 to count... but I'm not sure how to group them... I've tried working with Case statements, but can only get as far as;

1. Having to create duplicate statements for each estimated recurrence (for example 3 match collections would require case 1-9 statements)... is there a way to just have the 3 case statements and then repeat the loop?

2. If I set Global=True, I will get all of the matches, grouped together as (1,1,1),(2,2,2),(3,3,3), however what I need to get is (1,2,3),(1,2,3),(1,2,3)

I'm guessing i need to incorporate arrays here somehow, but I've never really used arrays in VBA before and so far haven't been able to figure out what I need to do. Something like

[LIST]
[*]<article>[INDENT]Dim arr
 arr = Array("Collection 1","Collection 2","Collection 3")

[/INDENT]
</article>
[/LIST]

My code at this point is simply a patchwork of random failed attempts and commented out bits and pieces that may or may not be on the right track. As I said, new to both Reg and Arrays in VBA, so i think posting it might be more confusing than helpful, but if you think that would help, let me know and I will.

Any help would be very much appreciated! Also, i don't know the etiquette for cross posting, I know it's not the best and should be avoided to prevent duplication of effort, is this what Trackback is for? I'm guessing yes, so I've put the URL of the site I mentioned above in the trackback field here and hopefully that helps... I will also let Diane know over on Slipstick that I've asked this question here as well.

Thanks,
Joe

 

[jbaich]

Maybe this pattern does work after all... still testing, but question remains about wildcard that would make pattern usable in either case?

Thanks,
Joe

 

[jbaich]

Actually nevermind that last one maybe... looks like it may work as is for either scenario.... still testing....

Thanks,
Joe

 

[jbaich]

So there's another pattern that could be useful to set a variable to something like true or false... example:

cPattern = "Meeting Preference: Conference Call"

if there's a match then variable sCall = "True"

which would be used later during the save routine to add something like "Call In: " to the folder name or something...

Would I want incorporate something like this as a standalone pattern search? or as another submatch? or using

Like

rather than Regexp?

If using regexp would it make sense to run this one first, set the variable then (would I need to?) clear the regexp object before proceeding with the current pattern match? something like...

cPattern = "Conference Call"
sPattern = "Area[:]\s*(\d*)\s*\r\nJurisdiction[:]\s*(\d*)\s*\r\nRoll.number[:]\s*((\w+(?:\.\d+)?))?\s*\r\n"   
    
    Set oRegExp = CreateObject("VBScript.RegExp")

    With oRegExp
        .Global = False
        .IgnoreCase = True
        .Pattern = cPattern
    End With



    If oRegExp.test(olMail.Body) Then
    sCall = "True"
        
End if

    Set oRegExp = New Object         'or = nothing?    ' or just exclude this altogether?


    With oRegExp
        .Global = True
        .IgnoreCase = True
        .Pattern = sPattern
        Set oMatchCollection = .Execute(olMail.Body)
    End With
''' continue on with the rest of the code as normal....?

Then during the save routine something like....

If sCall = "True" then
sName = "Call In: " & sName    ''' if not then sName would just be set up the same as it is now, without the addition of "Call In: "
End if

Since this wouldn't really require a collection or multiple matches, would something other than regex (Like?) be a better option? or since the macro is already running on regex does that make more sense? and if so am I on the right track above?

Thanks,
Joe

 

[Domenic]

Unclear... If you are going to proceed with matching sPattern only if there's a match with cPattern, then you can avoid creating an instance of the RegExp object and use InStr instead...

If InStr(1, olMail.Body, cPattern, vbTextCompare) > 0 Then

Note that for a case-sensitive search you can use vbBinaryCompare instead.

 

[jbaich]

Sorry if I was unclear, matching spattern and cpattern would be mutually exclusive... cpattern would simply need to be a yes/no, true/false condition that would either add a descriptive string to the folder name during the save so that the folders could more easily be sorted based on whether or not this pattern existed in the message... basically there is a field in the form/table that asks if the sender would prefer face to face meetings or conference calls... if they chose conference calls, it would be helpful to be able to add something like "call" to the folder name so that we could easily see at a glance which folders needed calls and which did not... so completely independent of matching sPattern. So maybe...

[COLOR=#333333]Dim Call as boolean

If InStr(1, olMail.Body, cPattern, vbTextCompare) > 0 Then
Call = True
End if

'Later during the save routine......

If Call = True Then
sPath = "Call " & Spath
End If
[/COLOR]

This way if Call = false, sPath would = sPath as it currently exists. I will try to test this out tomorrow and see if it works!

Thanks,
Joe

 

[jbaich]

Hey Domenic, every thing is working great, but I've been trying to figure out how to modify the pattern to be a little more broad... due to the lack of any kind of validation mask, users have been entering additional patterns in like: 12345.126-156, a2345-126-15x, 12345-126-156... I've tried to reverse engineer your pattern a bit to see how all the pieces work, but I think I still don't have a grasp on question marks... anyways, just wondering if you can see a better option than (\w*.\w*.\w*) or (\w*.+?) or (\w*.*)..?

I'm glad to hear that you've got the code working. That's great.

For your pattern, try...

sPattern = "Area[:]\s*\r\n(\d*)\s*\r\nJurisdiction[:]\s*\r\n(\d*)\s*\r\nRoll Number[:]\s*\r\n[B][COLOR=#ff0000]((\w+(?:\.\d+)?))?\s*\r\n[/COLOR][/B]"

For example, for the roll number it should match...

    empty string
    0
    123456789
    123456789.123
    A123456789.123456789
    123b46789.123456789

For example, for the roll number it should reject...

    .2
    .245
    .2456789
    .245.345
    1234.
    245.345.789
    A123456789.123C

Note that if the roll number is missing and you try adding the match to a collection, you'll get an error. That's because the roll number is used as the key, and you can't use an empty variable to assign the key.

Hope this helps!

Thanks,
joe

 

[Domenic]

Try...

sPattern = "Area[:]\s*\r\n(\d*)\s*\r\nJurisdiction[:]\s*\r\n(\d*)\s*\r\nRoll Number[:]\s*\r\n[COLOR=#ff0000]((?:\w+[.-])?(?:\w+[.-])?\w+)?[/COLOR]\s*\r\n"

Hope this helps!