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

Users are saying about us...

  1. 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

  2. David Chadwell

    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.

  3. Court Jester

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube