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]

now i seem to be getting run-time error'5020': Method "Execute of object 'IRegExp2' failed on the

  Set oMatchCollection = .Execute(olMail.Body)

line... not sure why

Thanks,
joe

 

[Domenic]

I went over your thread and I think I see the problem. It looks like the data is in table form, not in plain text. Can you please confirm that this is the case?

 

[Domenic]

If your data is in fact in table form, then olMail.Body would return the following text...

Area:
22
Jurisdiction:
205
Roll Number:
111111
 
 
Area:
22
Jurisdiction:
205
Roll Number:
111112

So as you can see the text returned by olMail.Body would contain more line breaks. Accordingly, I think the following pattern can be used...

sPattern = "Area[:]\s*\r\n(\d*)\r\nJurisdiction[:]\s*\r\n(\d*)\r\nRoll Number[:]\s*\r\n(\d*)"

Does this help?

 

[jbaich]

You're right, the info is in a table, but I'm still not getting any matches... this is my latest version...

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 olMail                      As Outlook.MailItem
    Dim oCol                        As New Collection
    Dim sPattern                    As String
    
   sPattern = "Area[:]\s*\r\n(\d*)\r\nJurisdiction[:]\s*\r\n(\d*)\r\nRoll Number[:]\s*\r\n(\d*)"
    Set olMail = Application.ActiveExplorer().Selection(1)
    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 = .Execute(olMail.Body)
    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

When I hover over the oMatch variable i see "oMatch = Nothing" and when I hover over the "oMatch.SubMatches(2)" variable i see ******** variable or with block variable not set>

not sure what that should be pointing me to...?

Thanks,
Joe

 

[Domenic]

Maybe we need to account for possible extra spaces. Can you simply do a copy and paste of your data so that I can take a look at it?

 

[jbaich]

Below is one example...

Amendment#	Panel	Hearing Date	NBHD	Act Use	Appraiser / Team	Appellant Type	Reason

<tbody>
</tbody>

Lots of boilerplate comments here........................................................................................................................................................................
Below is the information we have received from you.

CONTACT AND OWNERSHIP INFORMATION
First name:	Joe
Last name:	Joe
Company:
Email:
Phone:	111-111-1111 Home
Address 1:	111 11th Avenue South
Address 2:
City:	Anywhere
Province:	Any Province
Country:	Canada
Postal Code:	A1A 1A1
Is this a new address?:
Is this an alternate address?:
I am:	the owner
I am, other:
If using agency, information:
PROPERTY INFORMATION
Area:	22
Jurisdiction:	205
Roll number:	00111111
Address:	111 11th Avenue South Anywhere Any Province
Legal description:
Area Office:	(22) Anywhere
GROUNDS FOR REVIEW
Ground(s):	General Comment
Other grounds:
Additional Comments:	More General Comments
Hearing Preference:
Agreed to conditions?:	YES
2ND PROPERTY INFORMATION
Area:	22
Jurisdiction:	205
Roll number:	00111112
Address:	112 11th Avenue South Anywhere Any Province
Legal description:
GROUNDS FOR REVIEW
Ground(s):	General Comments
Other grounds:
Additional Comments:	More general comments

<tbody>
</tbody>

Lots more boilerplate comments down here................................................................................

 

[Domenic]

That's great. Thanks for the excellent representation of your data. That definitely helps. Yeah, the issue has to do with a space occurring after the Area number and the Jurisdiction number. So I've amended the pattern accordingly. I also allowed for any possible spaces after the Roll number, just in case. Try...

sPattern = "Area[:]\s*\r\n(\d*)\s*\r\nJurisdiction[:]\s*\r\n(\d*)\s*\r\nRoll Number[:]\s*\r\n(\d*)\s*"

Does this help?

 

[jbaich]

now i'm getting the "run-time error - '-1525661691 (a5104005)': The Operation Failed" at line: Set oMatchCollection = .Execute(olMail.Body) in the following section

With oRegExp        .Global = True
        .IgnoreCase = True
        .Pattern = sPattern
        'Set oMatchCollection = .Execute(Range("A1").Value)
        'Set olMail = Application.ActiveExplorer().Selection(1)
        Set oMatchCollection = .Execute(olMail.Body)
    End With

Thanks,
Joe

 

[jbaich]

also, the roll number pattern could contain alphanumeric characters... doesn't impact this example at all, but just thought i'd throw that out there

Would it help to convert to rich text at the beginning of the sub?

Thanks,
Joe

 

[Domenic]

Okay, I've adjusted the pattern to take into consideration alphanumeric characters in the Roll number. Actually, I see that you already had \w* in one of your posts at the beginning of this thread. In any case, in the following macro, you'll need to make sure that you set a reference to both Outlook and VBScript. You can, of course, change it so that you use late binding. I've tested it and it seems to work fine. If you continue to have problems, give me the details and I'll have a look at it tomorrow.

Option Explicit

Sub test()

    'Outlook variables
    Dim olApp                       As Outlook.Application
    Dim olMail                      As Outlook.MailItem

    'VBScript variables
    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
    
    'Excel variables
    Dim oCol                        As New Collection
    Dim sPattern                    As String
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.ActiveExplorer.Selection.Item(1)
    
    sPattern = "Area[:]\s*\r\n(\d*)\s*\r\nJurisdiction[:]\s*\r\n(\d*)\s*\r\nRoll Number[:]\s*\r\n(\w*)"

    Set oRegExp = CreateObject("VBScript.RegExp")
    
    With oRegExp
        .Global = True
        .IgnoreCase = True
        .Pattern = sPattern
        Set oMatchCollection = .Execute(olMail.Body)
    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