title: Using RegExp in Access VBA
body: <p>I cant seem to get the syntax right for using the <code>RegExp</code> function in VBA this is what I have, can anyone show me where I am going wrong or some examples of usage in VBA. I don't understand how I assign the matches found by RegExp into a <code>variable</code> or <code>array()</code>. I get an Error saying wrong number of arguments when it hits the RegEx.Execute</p>
  
  <pre><code>Dim RegEx As New RegExp
  Dim textOrders As String
  Dim RawText as String
  
  FileNum = FreeFile()
  Open FileName For Input As #FileNum
  Do Until EOF(FileNum)
  Input #FileNum, ShortText
  
  RawText = RawText &amp; ShortText
  
  loop
  
  With RegEx
      .Global = True
      .MultiLine = False
      .IgnoreCase = True
      .Pattern = ("ORDER NUMBER :\s+[^\s]+\s+Ship Date:\s+[^\s]+\n\n\s+Style Desc : .+\n\s+Linecode : .+\n\s+Qty\s+.+")
  
  End With
  
  textOrders = RegEx.Execute(RawText)  *This is where the error occurs*
  </code></pre>
  
  <p>I then tried this but still no luck</p>
  
  <pre><code>Sub GetMatches(ByRef Str As String, ByRef coll As Collection)
  
  Dim rExp As Object, rMatch As Object
  
  Set rExp = CreateObject("vbscript.regexp")
  With rExp
      .Global = True
      .Pattern = "ORDER NUMBER :\s+[^\s]+\s+Ship Date:\s+[^\s]+\n\n\s+Style Desc : .+\n\s+Linecode : .+\n\s+Qty\s+.+"
  End With
  
  Set rMatch = rExp.Execute(Str)
  If rMatch.Count &gt; 0 Then
      For Each rMatch In rMatch
          coll.Add rMatch.Value
          Debug.Print rMatch.Value
      Next rMatch
  End If
  Debug.Print ""
  End Sub
  </code></pre>
  
  <p>I then tried this: in <code>JavaScript</code></p>
  
  <pre><code>Public Sub x()
       Dim o As New ScriptControl
  
       Dim OriginalText As String
       Dim RegExPattern As String
  
       OriginalText = ""
  
       RegExPattern = ""
       Str = "ORDER NUMBER :   M0773175            Ship Date: 23-Nov-15" &amp; _
              "Style Desc : BLACKOUT CURTAINS CR 46X54" &amp; _
              "Linecode:        M878966" &amp; _
              "Qty 36"
  
       o.Language = "JScript"
       With o
           .AddCode "Function x() {return m(0);}" &amp; _
                      "var str = Str;" &amp; _
                      "var re = '/ORDER NUMBER :\s+[^\s]+\s+Ship Date:\s+[^\s]+\n\n\s+Style Desc : .+\n\s+Linecode : .+\n\s+Qty\s+.+/g';" &amp; _
                      "var m;" &amp; _
                      "while ((m = re.exec(str)) !== null) {" &amp; _
                          "if (m.index === re.lastIndex) {" &amp; _
                          "re.lastIndex++;" &amp; _
                          "};" &amp; _
                      "};"
  
           Debug.Print .run("x", 1, 2)
       End With
   End Sub
  </code></pre>
  
  <p>Sample Text of pattern trying to find:</p>
  
  <pre><code>ORDER NUMBER :   M0773175            Ship Date: 23-Nov-15
  
        Style Desc : BLACKOUT CURTAINS CR 46X54
        Linecode : M85466
        Qty              36
  </code></pre>
  
  <p>Sample of input text to search: it contains 9 orders but could contain 50 or 10 dynamic amount each time that's why trying to use regex</p>
  
  <pre><code>Purchase Order Supplier Copy                                                                   Printed Date: 18/08/2015 3.32 PM              Page 1 of         15
  
    ORDER DETAILS                                                    INVOICE ADDRESS             SHIPMENT &amp; PAYMENT TERMS   STYLE DETAILS
  
   DATE: 18-AUG-15                        ORDER NUMBERS:             Purchase Ledger             COM:CHINA                            STYLE DESCRIPTION             SUPPLIER STYLE REF    LINECODE
   ORDER TYPE: Standard                   M0773175   M0773177        Retail Ltd              COO:UK
   SEASON: S16                            M0773180   M0773183        Perimeter Road              Port:UK                              BLACKOUT CURTAINS CR 46X54    HRW14CT001            M878966
   DIVISION: HOMEWARE                     M0773186   M0773189        Knowsley Industrial Park    Factory:                             BLACKOUT CURTAINS CR 66X72    HRW14CT001            M878967
   DEPT: HOME FURNISHINGS                 M0773192   M0773195        Liverpool                   Buying Terms:                        BLACKOUT CURTAINS CR 90X90    HRW14CT001            M878972
   BUYER: Harpal Rai-Hilton               M0773198                   United Kingdom              Payment Terms:ENDMONTH           BLACKOUT CURTAINS GR 46X54    HRW14CT001            M878974
   SUPPLIER:                                         L33 7SZ                     Shipping Method:LAND                 BLACKOUT CURTAINS GR 66X72    HRW14CT001            M878975
   AGENT: 9999 N/A                                                   0151                        Pack Desc:Solid Boxed                BLACKOUT CURTAINS GR 90X90    HRW14CT001            M878976
                                                                                                 Currency:STERL
                                                                                                                                      BLACKOUT CURTAINS BG 46X54    HRW14CT001            M878977
                                                                                                 FOB value:5086.62
                                                                                                                                      BLACKOUT CURTAINS BG 66X72    HRW14CT001            M878978
                                                                                                                                      BLACKOUT CURTAINS BG 90X90    HRW14CT001            M878979
  
     PRODUCTION SUMMARY
  
     GRAND TOTAL
  
    ORDER NUMBER :   M0773175            Ship Date: 23-Nov-15
  
    Style Desc : BLACKOUT CURTAINS CR 46X54
    Linecode : M878966
    Qty              36
  
    ORDER NUMBER :   M0773177            Ship Date: 23-Nov-15
  
    Style Desc : BLACKOUT CURTAINS CR 66X72
    Linecode : M878967
    Qty              27
  
    ORDER NUMBER :   M0773180            Ship Date: 23-Nov-15
  
    Style Desc : BLACKOUT CURTAINS CR 90X90
    Linecode : M878972
    Qty              38
  
    ORDER NUMBER :   M0773183            Ship Date: 23-Nov-15
  
    Style Desc : BLACKOUT CURTAINS GR 46X54
    Linecode : M878974
    Qty              30
  
    ORDER NUMBER :   M0773186            Ship Date: 23-Nov-15
  
    Style Desc : BLACKOUT CURTAINS GR 66X72
    Linecode : M878975
    Qty              15
  
    ORDER NUMBER :   M0773189            Ship Date: 23-Nov-15
  
    Style Desc : BLACKOUT CURTAINS GR 90X90
    Linecode : M878976
    Qty              60
  
    ORDER NUMBER :   M0773192            Ship Date: 23-Nov-15
  
    Style Desc : BLACKOUT CURTAINS BG 46X54
    Linecode : M878977
    Qty              42
  
    ORDER NUMBER :   M0773195            Ship Date: 23-Nov-15
  
    Style Desc : BLACKOUT CURTAINS BG 66X72
    Linecode : M878978
    Qty              21
  
    ORDER NUMBER :   M0773198            Ship Date: 23-Nov-15
  
    Style Desc : BLACKOUT CURTAINS BG 90X90
    Linecode : M878979
    Qty              40
  </code></pre>