How To Replace text in a text file using VBA in Microsoft Excel?

In this article, you will learn how to ** replace text in a text file using VBA** in Microsoft Excel. Let’s see them below!! Get the official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Example

• Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
• Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic

• Now, you have to copy and paste the code given below.

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

• You need to save the code by selecting it and then close the window.

Save the Code

• Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
• You need to choose the Macros option in the Code section.

Choose Macro option

• Now, you have to make sure that your macro name is selected and click the *Run * button.

Run the Code

• Finally, you will receive output as replace text in a text file using VBA.

End of the Line

We hope that this tutorial gives you guidelines on how to ** replace text in a text file using VBA** in Microsoft Excel. Please leave a comment in case of any queries, and don’t forget to mention your valuable suggestions as well. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *Read more on Excel Formulas *!!

Read Next:

• Excel Formulas to Find and Replace Multiple Values ~ Quickly!!
• shortcut to Find and Replace, Replace Selected in Microsoft Excel 365!
• Excel Formulas to Replace One Character with Another ~ Quickly!!
• How to Find and Replace Text in Comments in Excel Office 365?
• How To Use Excel REPLACE Function in Office 365 with Examples?