Regular Expression using VBA in Excel not matching as expected

Question

I am trying to parse a CSV file using a regular expression. I open the file and read it into a string. The regular expression string "([\s\S]*?)" matches on the website http://www.regexr.com/. The locals window states the FirstIndex item is at location 172. The very first characters in the file consist of "Plugin","Plugin Name". Not sure what I'm missing??

Dim Matches
Dim objectRegularExp As RegExp
Dim Match

If Application.FileDialog(msoFileDialogOpen).Show <> -1 Then Exit Sub
FileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Open FileName For Input As #1
yourText = Input(LOF(1), #1)
Close #1
LengthOfFile = Len(yourText)
Set objectRegularExp = New RegExp
With objectRegularExp   ' "([\s\S]*?)"
     .Pattern = Chr(34) & Chr(40) & Chr(92) & Chr(115) & Chr(92) & Chr(83) & Chr(42) & Chr(63) & Chr(41) & Chr(34)
     .Global = True
     .MultiLine = True
End With

Set Matches = objectRegularExp.Execute(yourText)

Yes, the line below is contains the pattern, represented with the Chr() . in the immediate window the pattern is as seen on the comment line. — user249611
– user249611, Commented Feb 21, 2015 at 23:38
I have a gotten the regular expression to match the first occurrence on the line with: .Pattern = Chr(94) & Chr(34) & "([\s\S]*?)" & Chr(34) this msdn article states you have to start matching at the beginning of the string msdn.microsoft.com/en-us/library/ms235254%28v=vs.90%29.aspx — user249611
– user249611, Commented Feb 22, 2015 at 0:22

Ron Rosenfeld · Accepted Answer · 2015-02-22 11:59:21Z

1

I am assuming from your pattern that you are trying to match strings enclosed by double-quote marks.

Your pattern is not what you think it is. Your string of Chr's --

"(\s\S*?)"

You are missing the [ ]

I would suggest

.Pattern = """([\s\S]*?)"""

Or, perhaps:

 .Pattern = Chr(34) & "([^" & Chr(34) & "]*)" & Chr(34)

OR

.Pattern = """([^""]*)"""

If you must use the string of Chr's, it would be:

.Pattern = Chr(34) & Chr(40) & Chr(91) & Chr(92) & Chr(115) & Chr(92) & Chr(83) & Chr(93) & Chr(42) & Chr(63) & Chr(41) & Chr(34)

edited Feb 22, 2015 at 11:59

answered Feb 22, 2015 at 1:24

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Regular Expression using VBA in Excel not matching as expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related