How To Replace text in a text file using VBA in Microsoft Excel?
Tags: toreplacetextinatext, vbamacros
Published: 2021-10-04 02:19:14

---
title: How To Replace text in a text file using VBA in Microsoft Excel?
published: true
date: 2021-10-01 10:51:36 UTC
tags: ToReplaceTextInAText,VBAMacros
canonical_url: https://geekexcel.com/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](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](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

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

![](https://geekexcel.com/wp-content/uploads/2021/10/Save-the-Code-3-1024x894.png)<figcaption>Save the Code</figcaption>

- 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](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

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

![](https://geekexcel.com/wp-content/uploads/2021/10/Run-the-Code-4.png)<figcaption>Run the Code</figcaption>

- 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](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Read Next:**

- **[Excel Formulas to Find and Replace Multiple Values ~ Quickly!!](https://geekexcel.com/excel-formulas-to-find-and-replace-multiple-values/)**
- **[shortcut to Find and Replace, Replace Selected in Microsoft Excel 365!](https://geekexcel.com/shortcut-to-find-and-replace-replace-selected-in-microsoft-excel-365/)**
- **[Excel Formulas to Replace One Character with Another ~ Quickly!!](https://geekexcel.com/excel-formulas-to-replace-one-character-with-another/)**
- **[How to Find and Replace Text in Comments in Excel Office 365?](https://geekexcel.com/how-to-find-and-replace-text-in-comments-in-excel-office-365/)**
- **[How To Use Excel REPLACE Function in Office 365 with Examples?](https://geekexcel.com/different-ways-to-replace-function-in-excel-2013/)**