[SOLVED] Search and replace text file content using VBA Regex


This Content is from Stack Overflow. Question asked by HiruD

I’m currently using this code below (Thanks Maco for that) to replace multiple text file without opening and replace them manually. It work pretty well, but I want it to be able to replace something like, for example, sometime the content contaning error, like:



I just want to remove the enter mark at the end of the line and the dash so that the word “exemtion” is correct. A simple macro in Notepad++ can do it easily but then you have to open each file manually to do it, not so efficient for hundred of file. Can somebody help me modified this code with Regex in it? I do some “research” about it but still don’t know where to put it in thatthe code below. Thanks.

Sub ReplaceStringInFile()

Dim objFSO As Object, objFil As Object, objFil2 As Object
Dim StrFileName As String, StrFolder As String, strAll As String, newFileText As String

Set objFSO = CreateObject("scripting.filesystemobject")
StrFolder = "c:macro"
StrFileName = Dir(StrFolder & "*.txt")

Do While StrFileName <> vbNullString
    Set objFil = objFSO.opentextfile(StrFolder & StrFileName)
    strAll = objFil.readall
    Set objFil2 = objFSO.createtextfile(StrFolder & StrFileName)
    'change this to that in text
    newFileText = Replace(strAll, "THIS", "THAT")
    'change from to to in text
    newFileText = Replace(newFileText, "from", "to")
    'write file with new text
    objFil2.Write newFileText
    StrFileName = Dir

End Sub


You can use this function to remove a specified pattern

Public Function removePattern(searchPattern As String, strText As String) As String

Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")

With regEx
    .Pattern = searchPattern
    .IgnoreCase = True
    .MultiLine = True
    .Global = True
    removePattern = .Replace(strText, vbNullString)
End With

End Function

Then add this line of code to your sub:

newFileText = removePattern("([\r\n" & Chr(11) & "]-)", strAll)

This pattern looks for all kinds of line breaks followed by a hypen.
Chr(11) looks for the soft return – there is no equivalent regex-placeholder. Therefore it has to be put there as string.

This Question was asked in StackOverflow by HiruD and Answered by Ike It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?