Results 1 to 2 of 2
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    WildReplace() - a RegExp demo (IE 5.x required)

    I was irrationally excited to read about the RegExp (Regular Expressions) object in an ASP book, and to find a way to reference it in VBA. I think this wildcard search-and-replace object has some very practical applications for data scrubbing.

    Let's suppose that I have a template that parses data from a government web site, and need to clean up a few tags in the HTML source. In particular, there are

    tags embedded randomly in table cells, and because the

    style font size is overriding the <TD> style font size, my output looks funky. To identify the correct

    tags, I need to indicate that they either are followed by an open parenthesis or by the letters IC. Using an interactive wildcard search, I can find "(

    )([(,IC])" and replace it with "2", for example. (The source data does not use closing </P> tags.) However, testing reveals that IC is not treated as a unit but instead will match either I or C. Also, the search is case sensitive, and thus must be run several times to account for all possibilities. Hmmm... Is this an application for the RegExp object?

    The syntax of the find string (Pattern property) is different than the interactive wildcard search, but just similar enough to be confusing. Details can be found <A target="_blank" HREF=>here</A>. The query string "(

    )((|IC)" and replace string "$2" successfully resolves these issues when the strings are fed directly into the function. It took some work, though, to find a fast way to modify the ActiveDocument. Here is the final version. (There's a bonus function to convert an ASCII character number to a searchable hex string.)
    Sub test()
    Dim aPara As Paragraph, lngCount As Long
    Set aPara = ActiveDocument.Paragraphs(1)
    Do Until aPara Is Nothing
    If InStr(1, aPara.Range.Text, "

    ", vbTextCompare) Then
    aPara.Range.Text = WildReplace(aPara.Range.Text, _

    )((|IC)", "$2")
    End If
    Set aPara = aPara.Next
    lngCount = lngCount + 1
    StatusBar = "Processed " & lngCount & " paragraphs"
    MsgBox "Done!"
    End Sub
    Public Function WildReplace(strExpression As String, strFind As String, _
    strReplace As String, Optional bolReplaceAll As Boolean = True, _
    Optional bolCaseSensitive As Boolean = False) As String
    'requires a reference to Microsoft VBScript Regular Expressions
    'requires VBScript 5 = IE 5.x
    'perform minimal parameter checking
    If (strExpression = vbNullString) Or (strFind = vbNullString) Then
    WildReplace = strExpression
    Exit Function
    End If
    Dim objRegExp As RegExp
    'instantiate RegExp object
    Set objRegExp = New RegExp
    objRegExp.IgnoreCase = Not bolCaseSensitive
    objRegExp.Global = bolReplaceAll
    objRegExp.Pattern = strFind
    WildReplace = objRegExp.Replace(strExpression, strReplace)
    End Function
    Public Function HexSubStr(lngAscii As Long) As String
    'returns a find string valid in WildReplace()
    If lngAscii < 255 Then
    HexSubStr = "x" & Format(Hex(lngAscii), "00")
    'this guarantees a usable, though possibly useless, find string
    HexSubStr = "xFF"
    End If
    End Function

    (I had to insert a space in these tags to be able to post them.)
    Attached Files Attached Files

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: WildReplace() - a RegExp demo (IE 5.x required)

    Semi-bad news: the technique used in this procedure will purge any character formatting in the paragraph; formatting applied to the paragraph as a whole is retained. The same result occurs if you rewrite the procedure and function to use the FormattedText property of the range.

    Also, if the last paragraph is replaced, an extra paragraph is added at the end of the document.

    So... it works best on strings or plaintext.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts