Regular Expression Module

  • Thread starter Thread starter robert_neville
  • Start date Start date
R

robert_neville

I am having trouble with my regular expression module. Apparently, MS
Access returns an object-defined error [5020]. The objective behind my
code involves iterating through a table with regular expression
patterns and replace arguments; then applying regular expression
object's replace method on a text file. The code follows below.
Please let me know if you have any ideas.

‘--------------------------------------------------------------------
Private Sub cmdReplace_Click()
‘--------------------------------------------------------------------
Dim dbsCurrent As DAO.Database
Dim strTableName As String
Dim strFileContents As String
Dim strSuffix As String

Dim strNewFileName As String

Set dbsCurrent = CurrentDb

If lstReplace.ListIndex < 0 Then
MsgBox "Please select a table"
lstReplace.SetFocus
Exit Sub
End If

If IsNull(Me!txtFile) Then
Beep
MsgBox "File name required."
Exit Sub
End If

If Len(Dir(Me!txtFile)) = 0 Then
Beep
MsgBox Me!txtFile & " file not found."
Exit Sub
End If

strTableName = lstReplace.Column(0, lstReplace.ListIndex)
strFileContents = FileContents(Me!txtFile)
strFileContents = fnFindandReplace(strTableName, strFileContents)

strSuffix = "_DELIMITED"
strNewFileName = Left(Me!txtFile, Len(Me!txtFile) - 4) & strSuffix
& ".txt"

Debug.Print "cmdReplace: NewFileName -> "; strNewFileName '
Debug Statement

intRet = OutputTextFile(strFileContents, strNewFileName)

End Sub

‘--------------------------------------------------------------------
Public Function fnFindandReplace(strTable As String, varFileContents
As Variant)
‘--------------------------------------------------------------------

Dim dbsCurrent As DAO.Database
Dim rstPatterns As DAO.Recordset
Dim strFind As String
Dim strReplace As String
Dim strFileContents As String
Dim blnMatchCase As Boolean

Set dbsCurrent = CurrentDb
Set rstPatterns = dbsCurrent.OpenRecordset(strTable)

' If IsNull(varFileContents) Then Exit Function

Dim objReg As VBScript_RegExp_55.RegExp
Set objReg = New VBScript_RegExp_55.RegExp

strFileContents = CStr(varFileContents)

With rstPatterns
rstPatterns.MoveFirst
'loop thru each row
Do While Not rstPatterns.EOF
If rstPatterns!Trigger = "Yes" Then
'loop thru each col
objReg.Pattern = rstPatterns!Pattern
blnMatchCase = rstPatterns!Case
' Set case insensitivity
objReg.IgnoreCase = Not (blnMatchCase)
objReg.Global = True
strReplace = Replace(rstPatterns!Replace, "|", "")
‘ERROR OCCURS BELOW
strFileContents = objReg.Replace(strFileContents,
strReplace)
strReplace = ""
End If
.MoveNext
Loop
End With

dbsCurrent.Close
Set objReg = Nothing
Set rstPatterns = Nothing
Set dbs = Nothing

End Function
 
Hi Robert,

AFAIK the RegExp object uses the following error codes:

Case 5008: S = "Illegal assignment"
Case 5017: S = "Syntax error in regular expression"
Case 5018: S = "Unexpected quantifier in regular expression"
Case 5019: S = "Expected ']' in regular expression"
Case 5020: S = "Expected ')' in regular expression"
Case 5021: S = "Invalid range in character set"

I don't know what 5008 is for, but the others all indicate a problem
with the syntax of your pattern. 5020 usually just means that you've
forgotten a closing parenthesis somewhere in the pattern (or maybe have
inadvertently escaped it so VBA is reading "\)" where you intended ")").

I am having trouble with my regular expression module. Apparently, MS
Access returns an object-defined error [5020]. The objective behind my
code involves iterating through a table with regular expression
patterns and replace arguments; then applying regular expression
object's replace method on a text file. The code follows below.
Please let me know if you have any ideas.

‘--------------------------------------------------------------------
Private Sub cmdReplace_Click()
‘--------------------------------------------------------------------
Dim dbsCurrent As DAO.Database
Dim strTableName As String
Dim strFileContents As String
Dim strSuffix As String

Dim strNewFileName As String

Set dbsCurrent = CurrentDb

If lstReplace.ListIndex < 0 Then
MsgBox "Please select a table"
lstReplace.SetFocus
Exit Sub
End If

If IsNull(Me!txtFile) Then
Beep
MsgBox "File name required."
Exit Sub
End If

If Len(Dir(Me!txtFile)) = 0 Then
Beep
MsgBox Me!txtFile & " file not found."
Exit Sub
End If

strTableName = lstReplace.Column(0, lstReplace.ListIndex)
strFileContents = FileContents(Me!txtFile)
strFileContents = fnFindandReplace(strTableName, strFileContents)

strSuffix = "_DELIMITED"
strNewFileName = Left(Me!txtFile, Len(Me!txtFile) - 4) & strSuffix
& ".txt"

Debug.Print "cmdReplace: NewFileName -> "; strNewFileName '
Debug Statement

intRet = OutputTextFile(strFileContents, strNewFileName)

End Sub

‘--------------------------------------------------------------------
Public Function fnFindandReplace(strTable As String, varFileContents
As Variant)
‘--------------------------------------------------------------------

Dim dbsCurrent As DAO.Database
Dim rstPatterns As DAO.Recordset
Dim strFind As String
Dim strReplace As String
Dim strFileContents As String
Dim blnMatchCase As Boolean

Set dbsCurrent = CurrentDb
Set rstPatterns = dbsCurrent.OpenRecordset(strTable)

' If IsNull(varFileContents) Then Exit Function

Dim objReg As VBScript_RegExp_55.RegExp
Set objReg = New VBScript_RegExp_55.RegExp

strFileContents = CStr(varFileContents)

With rstPatterns
rstPatterns.MoveFirst
'loop thru each row
Do While Not rstPatterns.EOF
If rstPatterns!Trigger = "Yes" Then
'loop thru each col
objReg.Pattern = rstPatterns!Pattern
blnMatchCase = rstPatterns!Case
' Set case insensitivity
objReg.IgnoreCase = Not (blnMatchCase)
objReg.Global = True
strReplace = Replace(rstPatterns!Replace, "|", "")
‘ERROR OCCURS BELOW
strFileContents = objReg.Replace(strFileContents,
strReplace)
strReplace = ""
End If
.MoveNext
Loop
End With

dbsCurrent.Close
Set objReg = Nothing
Set rstPatterns = Nothing
Set dbs = Nothing

End Function
Click to expand...
 
On Thu, 06 Nov 2003 05:46:54 +0000, John Nurick

John,

Your advice helped a great deal. An error existed in the pattern. Yet
I have some additional inquires.

1. Does a utility exist for testing regular expressions patterns and
replace agreements? Such a utility would help distinguish between
coding errors and regex pattern errors.

2. My regular expression patterns exist in an Access table in a Memo
field (due to the pattern length, text fields do not work). Do I need
to convert the data in a memo field to string data (e.g. cstr())?
Plus, the carriage returns and line feed in the memo field pass to the
regex replace function. How do I deal with situation using line
continuation? Sorry, I do not know how to clarify this scenario
further. Hopefully, you understand the situation.

3. Where did you find these error number definitions?

Example of the Pattern
"([A-Z]*?)(?:\t)([A-Z]*?)(?:\t)(\d{3}/\d{3}-\d{4})(?: \(o\))(?:\r\n)"
_
& "(?:\t)(.*?)(?:\t)(\d{3}/\d{3}-\d{4})(?: \(f\))(?:\r\n)" _
& "(?:\t)(.*?)(?:\r\n)" _
& "(?:\t)(.*?)(?:,\s)([A-Z]{2})(?:\s)(\d{5})(?:\r\n)"

Example of Replacement Pattern
"Job Title:"$1" & vbCrLf _
& "Contact Name:"$2" & vbCrLf _
& "Business Phone:"$3" & vbCrLf _
& "Company:"$4" & vbCrLf _
& "Business Fax:"$5" & vbCrLf _
& "Business Street:"$6" & vbCrLf _
& "Business City:"$7"& vbCrLf _
& "Business State:"$8" & vbCrLf _
& "Business Postal Code:"$9" & vbCrLf




Hi Robert,

AFAIK the RegExp object uses the following error codes:

Case 5008: S = "Illegal assignment"
Case 5017: S = "Syntax error in regular expression"
Case 5018: S = "Unexpected quantifier in regular expression"
Case 5019: S = "Expected ']' in regular expression"
Case 5020: S = "Expected ')' in regular expression"
Case 5021: S = "Invalid range in character set"

I don't know what 5008 is for, but the others all indicate a problem
with the syntax of your pattern. 5020 usually just means that you've
forgotten a closing parenthesis somewhere in the pattern (or maybe have
inadvertently escaped it so VBA is reading "\)" where you intended ")").

I am having trouble with my regular expression module. Apparently, MS
Access returns an object-defined error [5020]. The objective behind my
code involves iterating through a table with regular expression
patterns and replace arguments; then applying regular expression
object's replace method on a text file. The code follows below.
Please let me know if you have any ideas.

‘--------------------------------------------------------------------
Private Sub cmdReplace_Click()
‘--------------------------------------------------------------------
Dim dbsCurrent As DAO.Database
Dim strTableName As String
Dim strFileContents As String
Dim strSuffix As String

Dim strNewFileName As String

Set dbsCurrent = CurrentDb

If lstReplace.ListIndex < 0 Then
MsgBox "Please select a table"
lstReplace.SetFocus
Exit Sub
End If

If IsNull(Me!txtFile) Then
Beep
MsgBox "File name required."
Exit Sub
End If

If Len(Dir(Me!txtFile)) = 0 Then
Beep
MsgBox Me!txtFile & " file not found."
Exit Sub
End If

strTableName = lstReplace.Column(0, lstReplace.ListIndex)
strFileContents = FileContents(Me!txtFile)
strFileContents = fnFindandReplace(strTableName, strFileContents)

strSuffix = "_DELIMITED"
strNewFileName = Left(Me!txtFile, Len(Me!txtFile) - 4) & strSuffix
& ".txt"

Debug.Print "cmdReplace: NewFileName -> "; strNewFileName '
Debug Statement

intRet = OutputTextFile(strFileContents, strNewFileName)

End Sub

‘--------------------------------------------------------------------
Public Function fnFindandReplace(strTable As String, varFileContents
As Variant)
‘--------------------------------------------------------------------

Dim dbsCurrent As DAO.Database
Dim rstPatterns As DAO.Recordset
Dim strFind As String
Dim strReplace As String
Dim strFileContents As String
Dim blnMatchCase As Boolean

Set dbsCurrent = CurrentDb
Set rstPatterns = dbsCurrent.OpenRecordset(strTable)

' If IsNull(varFileContents) Then Exit Function

Dim objReg As VBScript_RegExp_55.RegExp
Set objReg = New VBScript_RegExp_55.RegExp

strFileContents = CStr(varFileContents)

With rstPatterns
rstPatterns.MoveFirst
'loop thru each row
Do While Not rstPatterns.EOF
If rstPatterns!Trigger = "Yes" Then
'loop thru each col
objReg.Pattern = rstPatterns!Pattern
blnMatchCase = rstPatterns!Case
' Set case insensitivity
objReg.IgnoreCase = Not (blnMatchCase)
objReg.Global = True
strReplace = Replace(rstPatterns!Replace, "|", "")
‘ERROR OCCURS BELOW
strFileContents = objReg.Replace(strFileContents,
strReplace)
strReplace = ""
End If
.MoveNext
Loop
End With

dbsCurrent.Close
Set objReg = Nothing
Set rstPatterns = Nothing
Set dbs = Nothing

End Function
Click to expand...
Click to expand...
 
1. Does a utility exist for testing regular expressions patterns and
replace agreements? Such a utility would help distinguish between
coding errors and regex pattern errors.
Click to expand...

Not that I'm aware of, though in theory it would be possible to create a
regular expression to do the job<g>. But if you put error traps in your
code and inspect the error messages you can usually get a good idea of
just what is causing a problem.
2. My regular expression patterns exist in an Access table in a Memo
field (due to the pattern length, text fields do not work). Do I need
to convert the data in a memo field to string data (e.g. cstr())?
Click to expand...

No; memo fields and text fields both store strings.
Plus, the carriage returns and line feed in the memo field pass to the
regex replace function. How do I deal with situation using line
continuation? Sorry, I do not know how to clarify this scenario
further. Hopefully, you understand the situation.
Click to expand...

I don't quite understand, because a VBS regular expression won't
normally include carriage returns and line feeds. If you want to include
these in the memo fields to make the expression easier to read, you'll
need to strip them out before passing them to the regexp object, e.g.
with something like:
strRegExp = Replace(strMemoField, vbCRLF, "")
3. Where did you find these error number definitions?
Click to expand...

I can't remember exactly, but it involved writing code that threw
thousands of error numbers at the RegExp object and caught any messages
that came back.

Example of the Pattern
"([A-Z]*?)(?:\t)([A-Z]*?)(?:\t)(\d{3}/\d{3}-\d{4})(?: \(o\))(?:\r\n)"
_
& "(?:\t)(.*?)(?:\t)(\d{3}/\d{3}-\d{4})(?: \(f\))(?:\r\n)" _
& "(?:\t)(.*?)(?:\r\n)" _
& "(?:\t)(.*?)(?:,\s)([A-Z]{2})(?:\s)(\d{5})(?:\r\n)"

Example of Replacement Pattern
"Job Title:"$1" & vbCrLf _
& "Contact Name:"$2" & vbCrLf _
& "Business Phone:"$3" & vbCrLf _
& "Company:"$4" & vbCrLf _
& "Business Fax:"$5" & vbCrLf _
& "Business Street:"$6" & vbCrLf _
& "Business City:"$7"& vbCrLf _
& "Business State:"$8" & vbCrLf _
& "Business Postal Code:"$9" & vbCrLf




Hi Robert,

AFAIK the RegExp object uses the following error codes:

Case 5008: S = "Illegal assignment"
Case 5017: S = "Syntax error in regular expression"
Case 5018: S = "Unexpected quantifier in regular expression"
Case 5019: S = "Expected ']' in regular expression"
Case 5020: S = "Expected ')' in regular expression"
Case 5021: S = "Invalid range in character set"

I don't know what 5008 is for, but the others all indicate a problem
with the syntax of your pattern. 5020 usually just means that you've
forgotten a closing parenthesis somewhere in the pattern (or maybe have
inadvertently escaped it so VBA is reading "\)" where you intended ")").

I am having trouble with my regular expression module. Apparently, MS
Access returns an object-defined error [5020]. The objective behind my
code involves iterating through a table with regular expression
patterns and replace arguments; then applying regular expression
object's replace method on a text file. The code follows below.
Please let me know if you have any ideas.

‘--------------------------------------------------------------------
Private Sub cmdReplace_Click()
‘--------------------------------------------------------------------
Dim dbsCurrent As DAO.Database
Dim strTableName As String
Dim strFileContents As String
Dim strSuffix As String

Dim strNewFileName As String

Set dbsCurrent = CurrentDb

If lstReplace.ListIndex < 0 Then
MsgBox "Please select a table"
lstReplace.SetFocus
Exit Sub
End If

If IsNull(Me!txtFile) Then
Beep
MsgBox "File name required."
Exit Sub
End If

If Len(Dir(Me!txtFile)) = 0 Then
Beep
MsgBox Me!txtFile & " file not found."
Exit Sub
End If

strTableName = lstReplace.Column(0, lstReplace.ListIndex)
strFileContents = FileContents(Me!txtFile)
strFileContents = fnFindandReplace(strTableName, strFileContents)

strSuffix = "_DELIMITED"
strNewFileName = Left(Me!txtFile, Len(Me!txtFile) - 4) & strSuffix
& ".txt"

Debug.Print "cmdReplace: NewFileName -> "; strNewFileName '
Debug Statement

intRet = OutputTextFile(strFileContents, strNewFileName)

End Sub

‘--------------------------------------------------------------------
Public Function fnFindandReplace(strTable As String, varFileContents
As Variant)
‘--------------------------------------------------------------------

Dim dbsCurrent As DAO.Database
Dim rstPatterns As DAO.Recordset
Dim strFind As String
Dim strReplace As String
Dim strFileContents As String
Dim blnMatchCase As Boolean

Set dbsCurrent = CurrentDb
Set rstPatterns = dbsCurrent.OpenRecordset(strTable)

' If IsNull(varFileContents) Then Exit Function

Dim objReg As VBScript_RegExp_55.RegExp
Set objReg = New VBScript_RegExp_55.RegExp

strFileContents = CStr(varFileContents)

With rstPatterns
rstPatterns.MoveFirst
'loop thru each row
Do While Not rstPatterns.EOF
If rstPatterns!Trigger = "Yes" Then
'loop thru each col
objReg.Pattern = rstPatterns!Pattern
blnMatchCase = rstPatterns!Case
' Set case insensitivity
objReg.IgnoreCase = Not (blnMatchCase)
objReg.Global = True
strReplace = Replace(rstPatterns!Replace, "|", "")
‘ERROR OCCURS BELOW
strFileContents = objReg.Replace(strFileContents,
strReplace)
strReplace = ""
End If
.MoveNext
Loop
End With

dbsCurrent.Close
Set objReg = Nothing
Set rstPatterns = Nothing
Set dbs = Nothing

End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top