We help IT Professionals succeed at work.
Get Started

Regular expressions in VBA, search for more than one ASCII Code

Last Modified: 2012-05-11
Hi Folks, should be some easy points up for grabs here, Im using regular expressions 5.5 to search and replace a string based on ASCII codes, I have created a procedure that calls the regexe code and replaces the ASCII Code with whitespace, If I want to search and replace two codes I currently call the procedure twice, when Im guessing that I can somehow edit the pattern to search for two codes, any examples would be great.
Sub Checkfile()
Dim FSO As New FileSystemObject
Dim fsoFile As File
'Open an XML file to search for illegal characters
Set SourceFile = FSO.OpenTextFile("C:\TestWithInvalidCharcter.xml")
'Create a new XML file that would not contain new xml file
FSO.CreateTextFile ("C:\Test.xml")
fnum = FreeFile()
Open "C:\Test.xml" For Append As fnum

Do While Not SourceFile.AtEndOfStream
    'replace any ascii code 240 with whitespace and print to the file
    Print #fnum, TestRegExp("\240 040", SourceFile.ReadLine)
        'replace any ascii code 040 with white space and print to the file
    Print #fnum, TestRegExp("\040", SourceFile.ReadLine)
Close fnum

End Sub
Function TestRegExp(myPattern As String, myString As String)
   'Create objects.
   Dim objRegExp As RegExp
   Dim objMatch As Match
   Dim colMatches   As MatchCollection
   Dim RetStr As String
   ' Create a regular expression object.
   Set objRegExp = New RegExp

   'Set the pattern by using the Pattern property.
   objRegExp.Pattern = myPattern

   ' Set Case Insensitivity.
   objRegExp.IgnoreCase = True

   'Set global applicability.
   objRegExp.Global = True
   'Test whether the String can be compared.
   If (objRegExp.Test(myString) = True) Then

   'Get the matches.
    Set colMatches = objRegExp.Execute(myString)   ' Execute search.

    For Each objMatch In colMatches   ' Iterate Matches collection.
           RetStr = objRegExp.Replace(myString, " ")
    RetStr = myString
   End If
   TestRegExp = RetStr
End Function

Open in new window

Watch Question
Most Valuable Expert 2011
Top Expert 2015
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE