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

 

[Fennek]

Hi,

if I understand you right, the question is about an OR of 3 pattern.

For this several pattern can be combined with the pipe-symbol "|" within one pattern, e.g. "[A-E]|\d{2,1}|\W".

regards

 

[jbaich]

Thanks for the quick reply! so these were my three pattern definitions...

 'Pattern1 = "(Area[:]\s*([\d]*))"
 'Pattern2 = "(Jurisdiction[:]\s*([\d]*))"
 'Pattern3 = "(Roll number[:]\s*((\w)*))"

Are you saying i could do something like this?

    Set Reg1 = New RegExp    
For i = 1 To 3


With Reg1
    Select Case i
    Case i
    .Pattern = "(Pattern1 | Pattern2 | Pattern3)"
    .Global = True


    End Select
End With

Thanks,
Joe

 

[Sektor]

Are you saying i could do something like this?

Yes

 

[Fennek]

Hi,

have you tsted this:

.Pattern = "(Area[:]\s*([\d]*))|(Jurisdiction[:]\s*([\d]*))|(Roll number[:]\s*((\w)*))"

regards

 

[jbaich]

so it's kind of working... most of the code near the bottom was from working with the individual submatches... so I need to figure out how to modify it to get it to use the larger pattern matches...

Here's what I'm working with at the moment, please forgive the trial and error nature of the code so far... some of these variables are ones I'm planning to build into later parts... if I can make it that far

Public strInList As String, FldrRoot As String, FldrLvl1 As String, FldrLvl2 As String, FldrLvl3 As String, FileRef As StringPublic xDirectFldrRoot$, InitialFoldr$


Sub GetValueUsingRegEx()
    Dim olMail As Outlook.MailItem
    Dim Reg1 As RegExp
    Dim M1 As MatchCollection
    Dim M As Match
    Dim strSubject As String
    Dim testSubject As String
    Dim MatchCount As Integer, answer As Integer


         
    Set olMail = Application.ActiveExplorer().Selection(1)
     
    Set Reg1 = New RegExp
    
For i = 1 To 3          '\\\\\\\\\\ I would like to replace 3 with a count variable to establish a dynamic upper limit as it will vary each time/////////////


With Reg1
    Select Case i
    
    Case i
    .Pattern = "(Area[:]\s*([\d]*))|(Jurisdiction[:]\s*([\d]*))|(Roll number[:]\s*((\w)*))"
    .Global = True
    
'    Case 1
'    .Pattern = "(Area[:]\s*([\d]*))"
'    .Global = False
'
'    Case 2
'    .Pattern = "(Jurisdiction[:]\s*([\d]*))"
'    .Global = False
'
'    Case 3
'    .Pattern = "(Roll number[:]\s*((\w)*))"
'    .Global = False
    End Select
End With
    
    
    If Reg1.test(olMail.Body) Then
     
        Set M1 = Reg1.Execute(olMail.Body)
        For Each M In M1
            Debug.Print M.SubMatches(1)
            strSubject = M.SubMatches(1)
            MatchCount = M1.Count
         strSubject = Replace(strSubject, Chr(13), "")
         testSubject = testSubject & Trim(strSubject)
         Debug.Print testSubject
         strInList = "IN " & testSubject
         Next
    End If
          
Next i
        If strInList = "" Then
        msgbox ("No references found, please ensure you have selected the correct message from your inbox")
        Exit Sub

        End If

So in the email I'm testing this on there are two separate instances of the Pattern = "(Area[:]\s*([\d]*))|(Jurisdiction[:]\s*([\d]*))|(Roll number[:]\s*((\w)*))"

Example:

The goal will to be to end up with variables containing values for each pattern, so in this case....

testSubject1 would be 2220500111111
testSubject2 would be 2220500111112

and so on... in this example there are only 2...


I will experiment with this and see what I can do, obviously if you can clearly see how i should write this, please don't hesitate to save me time

Thanks,
Joe

 

[Domenic]

In order to achieve the grouping (1,2,3),(1,2,3),(1,2,3), I think you'll need to use the following...

.Pattern = "Area[:]\s*(\d*)\nJurisdiction[:]\s*(\d*)\nRoll Number[:]\s*(\d*)"

Although, I'm sure it can be improved. For one thing, if you know that a number is going to 2 digits in length, I would use \d{2} instead of \d*. Or, if a number is going to be 2 to 5 digits in length, I would use \d{2,5} instead of \d*. But it probably won't make a difference in this case.

Then, instead of using an array to store your matches, I would use a Collection. Here's an example that I hope will help...

Option Explicit

Sub test()

    Dim oRegExp                     As VBScript_RegExp_55.RegExp
    Dim oMatchCollection            As VBScript_RegExp_55.MatchCollection
    Dim oSubMatches                 As VBScript_RegExp_55.SubMatches
    Dim oMatch                      As VBScript_RegExp_55.Match
    
    Dim oCol                        As New Collection
    Dim sPattern                    As String
    
    sPattern = "Area[:]\s*(\d*)\nJurisdiction[:]\s*(\d*)\nRoll Number[:]\s*(\d*)"
    
    Set oRegExp = New VBScript_RegExp_55.RegExp
    
    With oRegExp
        .Global = True
        .IgnoreCase = True
        .Pattern = sPattern
        Set oMatchCollection = .Execute(Range("A1").Value)
    End With
    
    For Each oMatch In oMatchCollection
        oCol.Add oMatch, oMatch.SubMatches(2)
    Next oMatch
    
    For Each oMatch In oCol
        Debug.Print oMatch.SubMatches(0), oMatch.SubMatches(1), oMatch.SubMatches(2)
    Next oMatch
    
End Sub

And, you can of course also refer to any of the matches within the collection by roll number...

oCol("00111111")

And submatches for the roll number...

oCol("00111111").SubMatches(0)

Hope this helps!

 

[jbaich]

Thanks Domenic! I will try this out, I'm using Office 2007... not sure if that will make a difference, but thought maybe I should mention that

Also, I'm not sure if you caught it in my original post, but I'm trying to use this for an Outlook macro... so I'm thinking I'm going to have to change Range("A1")

Set oMatchCollection = .Execute([B]Range("A1")[/B].Value)

to something else... i was hoping maybe this

Set oMatchCollection = [B]Reg1[/B].Execute(olMail.Body)

would work, but no luck...

tried changing Reg1 to oRegExp

Set oMatchCollection = [B]oRegExp[/B].Execute(olMail.Body)

but i get Object variable or With block not set... run-time error '91'

I did declare olMail, so that's not it...

Sub test()

    Dim oRegExp                     As VBScript_RegExp_55.RegExp
    Dim oMatchCollection            As VBScript_RegExp_55.MatchCollection
    Dim oSubMatches                 As VBScript_RegExp_55.SubMatches
    Dim oMatch                      As VBScript_RegExp_55.Match
[B]     Dim olMail                           As Outlook.MailItem[/B]
    Dim oCol                        As New Collection
    Dim sPattern                    As String
    
    sPattern = "Area[:]\s*(\d*)\nJurisdiction[:]\s*(\d*)\nRoll Number[:]\s*(\d*)"

is it a compatibility issue with 2007 or is the VBA for excel slightly different from Outlook and I'm missing it?

Thanks,
Joe

 

[Domenic]

Try...

sPattern = "Area[:]\s*(\d*)[COLOR=#ff0000]\r\n[/COLOR]Jurisdiction[:]\s*(\d*)[COLOR=#ff0000]\r\n[/COLOR]Roll Number[:]\s*(\d*)"

Does this help?

 

[jbaich]

nope, but i think it does have something to do with the pattern syntax as I've been messing with it and getting partial results, however that last one produces zero sub matches...

So to get through the macro without debugging I've modified slightly, I changed the bolded bits....

Option Explicit

Sub test()


    Dim oRegExp                     As VBScript_RegExp_55.RegExp
    Dim oMatchCollection            As VBScript_RegExp_55.MatchCollection
    Dim oSubMatches                 As VBScript_RegExp_55.SubMatches
    Dim oMatch                      As VBScript_RegExp_55.Match
[B]    Dim olMail                      As Outlook.MailItem[/B]
    Dim oCol                        As New Collection
    Dim sPattern                    As String
    
   sPattern = "Area[:]\s*(\d*)\r\nJurisdiction[:]\s*(\d*)\r\nRoll Number[:]\s*(\d*)"
    
[B]    Set olMail = Application.ActiveExplorer().Selection(1)[/B]
    Set oRegExp = New VBScript_RegExp_55.RegExp
    
    With oRegExp
        .Global = True
        .IgnoreCase = True
        .Pattern = sPattern
        'Set oMatchCollection = .Execute(Range("A1").Value)
        'Set olMail = Application.ActiveExplorer().Selection(1)
        Set oMatchCollection = [B].Execute(olMail.Body)[/B]
    End With
    
    For Each oMatch In oMatchCollection
        oCol.Add oMatch, oMatch.SubMatches(2)
    Next oMatch
    
    For Each oMatch In oCol
        Debug.Print oMatch.SubMatches(0), oMatch.SubMatches(1), oMatch.SubMatches(2)
    Next oMatch
    
End Sub

by tinkering with the spattern i've been able to get one or two submatches, but not at the same time and no submatches as is above...

Thanks,
Joe