CODEWITHSUNDEEP
regexexcelvba
user249611
user249611

Reputation: 65

Regular Expression using VBA in Excel not matching as expected

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)

Upvotes: 1

Views: 731

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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)

Upvotes: 1

Related Questions