I cant seem to get the syntax right for using the RegExp 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 variable or array(). I get an Error saying wrong number of arguments when it hits the RegEx.Execute
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 & 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*
I then tried this but still no luck
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 > 0 Then
For Each rMatch In rMatch
coll.Add rMatch.Value
Debug.Print rMatch.Value
Next rMatch
End If
Debug.Print ""
End Sub
I then tried this: in JavaScript
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" & _
"Style Desc : BLACKOUT CURTAINS CR 46X54" & _
"Linecode: M878966" & _
"Qty 36"
o.Language = "JScript"
With o
.AddCode "Function x() {return m(0);}" & _
"var str = Str;" & _
"var re = '/ORDER NUMBER :\s+[^\s]+\s+Ship Date:\s+[^\s]+\n\n\s+Style Desc : .+\n\s+Linecode : .+\n\s+Qty\s+.+/g';" & _
"var m;" & _
"while ((m = re.exec(str)) !== null) {" & _
"if (m.index === re.lastIndex) {" & _
"re.lastIndex++;" & _
"};" & _
"};"
Debug.Print .run("x", 1, 2)
End With
End Sub
Sample Text of pattern trying to find:
ORDER NUMBER : M0773175 Ship Date: 23-Nov-15
Style Desc : BLACKOUT CURTAINS CR 46X54
Linecode : M85466
Qty 36
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
Purchase Order Supplier Copy Printed Date: 18/08/2015 3.32 PM Page 1 of 15
ORDER DETAILS INVOICE ADDRESS SHIPMENT & 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
