Search and replace text file content using VBA Regex

I'm using the code below (Thanks Maco for that) to replace text in multiple text files without opening and replacing manually.

It works, but I want to be able to replace content containing an error, like:

exem
-tion

I want to remove the enter mark at the end of the line and the dash so that the text becomes

exemtion

A simple macro in Notepad++ can do it easily but then you have to open each file manually, not so efficient for hundreds of files.

Can this be modified with Regex in it?

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
        objFil.Close
        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
        objFil2.Close
        StrFileName = Dir
    Loop
End Sub

Solution

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.