Replace text in a text file using VBA in Microsoft Excel

The macros below can be used to replace text in a text file,
e.g. when you want to change a column separator in a text file
before you import it into an Excel worksheet or after you export a worksheet to a text file.

Sub ReplaceTextInFile(SourceFile As String, _
    sText As String, rText As String)
Dim TargetFile As String, tLine As String, tString As String
Dim p As Integer, i As Long, F1 As Integer, F2 As Integer
    TargetFile = "RESULT.TMP"
    If Dir(SourceFile) = "" Then Exit Sub
    If Dir(TargetFile) <> "" Then
        On Error Resume Next
        Kill TargetFile
        On Error GoTo 0
        If Dir(TargetFile) <> "" Then
            MsgBox TargetFile & _
                " already open, close and delete / rename the file and try again.", _
                vbCritical
            Exit Sub
        End If
    End If
    F1 = FreeFile
    Open SourceFile For Input As F1
    F2 = FreeFile
    Open TargetFile For Output As F2
    i = 1 ' line counter
    Application.StatusBar = "Reading data from " & _
        TargetFile & " ..."
    While Not EOF(F1)
        If i Mod 100 = 0 Then Application.StatusBar = _
            "Reading line #" & i & " in " & _
            TargetFile & " ..."
        Line Input #F1, tLine
        If sText <> "" Then
            ReplaceTextInString tLine, sText, rText
        End If
        Print #F2, tLine
        i = i + 1
    Wend
    Application.StatusBar = "Closing files ..."
    Close F1
    Close F2
    Kill SourceFile ' delete original file
    Name TargetFile As SourceFile ' rename temporary file
    Application.StatusBar = False
End Sub

Private Sub ReplaceTextInString(SourceString As String, _
    SearchString As String, ReplaceString As String)
Dim p As Integer, NewString As String
    Do
        p = InStr(p + 1, UCase(SourceString), UCase(SearchString))
        If p > 0 Then ' replace SearchString with ReplaceString
            NewString = ""
            If p > 1 Then NewString = Mid(SourceString, 1, p - 1)
            NewString = NewString + ReplaceString
            NewString = NewString + Mid(SourceString, _
                p + Len(SearchString), Len(SourceString))
            p = p + Len(ReplaceString) - 1
            SourceString = NewString
        End If
        If p >= Len(NewString) Then p = 0
    Loop Until p = 0
End Sub

Sub TestReplaceTextInFile()
    ReplaceTextInFile ThisWorkbook.Path & _
        "\ReplaceInTextFile.txt", "|", ";"
    ' replaces all pipe-characters (|) with semicolons (;) 
End Sub

Court Jester
May 8, 2015 at 9:56 pm

I forgot to say I only have access to excel 2007
Court Jester

Reply ↓
Court Jester
May 8, 2015 at 9:54 pm

I would like to create a template which fills in data. It must be in sequence - start in a cell to fill in customer name then enter takes me to the next line which is address line and so on. Is there a simple way for that?
Thanks in advance
Court Jester

Reply ↓
David Chadwell
June 6, 2014 at 2:19 pm

I am trying to write a macro to replace a string of characters (like a part number)in a workbook with a new string of characters, all of which are on a separate spreadsheet. There are 25,000 pairs of strings to replace on the spreadsheet, and the one to be replaced could be on any sheet of a workbook. I have 9000 workbooks to do.

Reply ↓
Matthieu
November 22, 2013 at 1:17 pm

Thanks very much for your code. I have used it in my code but I had to fix a "little" issue. The code does not work properly if the ReplaceString is of Length = 0 and the SourceString is at the begining of the Line.
Therefore, here is the fix I have done to handle that situation :

Private Sub ReplaceTextInString(SourceString As String, SearchString As String, ReplaceString As String, NumberofCommas As Integer)
Dim p As Integer, NewString As String
p = 0
Do
p = InStr(p + 1, UCase(SourceString), UCase(SearchString))
If p = 0 Then p = -1
If p > 0 Then ' replace SearchString with ReplaceString
NewString = ""
If p > 1 Then NewString = Mid(SourceString, 1, p - 1) 'OK this is to have the first part of the string
NewString = NewString + ReplaceString ' OK this postfixes the replacing string to the first part of the string
NewString = NewString + Mid(SourceString, p + Len(SearchString), Len(SourceString)) 'OK this is valid
p = p - 1 + Len(ReplaceString) ' we are setting the p to its actual (current match) + the length of the ReplaceString - 1. OK Correct.
SourceString = NewString
End If
If p >= Len(NewString) Then p = -1
Loop Until p = -1
End Sub

Reply ↓