How to Open a Word Document and Replace Text with Excel VBA (6 Examples)

In the following sections, we will provide six examples of how to open a Word document and replace text using VBA in Excel.

Some things to remember:

Save the Word document in “.docm” format.

Open your VBA editor by pressing Alt+F11 on your keyboard. Click on Insert > Module. Enter your codes.

Method 1 – Open a Word Document and Replace a Text with Excel VBA

Steps:

Name your Word document “List.docm”.

Here are the names of some electronic products. Using the VBA code in Excel, we will replace the text “TV” with “Television” from this list.

Make sure the Word document is in “.docm” format.
In your VBA editor, go to Tools > References.

Enable the option “Microsoft Word 16.0 Object Library” and click OK.

Open your VBA editor and enter the following code:

Sub open_word_replace_text()

Dim book1 As Word.Application
Dim sheet1 As Word.Document

Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("D:\SOFTEKO\List.docm")

With sheet1.Content.Find
 .Text = "TV"
 .Replacement.Text = "TELEVISION"
 .Wrap = wdFindContinue
 .Execute Replace:=wdReplaceAll
End With

End Sub

Breakdown of the VBA Code

Here, the book1 is stored as a Word application.
Sheet1 is stored as a Word document.
Visible = True: It will make sure that our Word document is visible.
Documents.Open(“D:\SOFTEKO\List.docm”): It will open your Word document. Make sure to change the file location to match your device.
With sheet1.Content.Find: It will start to find the content from the document. Utilizing the WITH statement, we will attempt to avoid the repetition of typing the object name sheet1.Content.Find.
.Text = “TV”: Searches for the text “TV”.
.Replacement.Text = “TELEVISION”: Replace the text with “TELEVISION”.
.Wrap = wdFindContinue: It will continue to search till the end of the document.
.Execute Replace:=wdReplaceAll: Finally, it will replace all the texts.

Run the code. It will open your Word document. You will see the following result:

Method 2 – Replace Text and Change Formatting from a Word Document

Steps:

Make sure the word document is in “.docm” format.
In your VBA editor, go to Tools > References.

Enable the option “Microsoft Word 16.0 Object Library” and click OK.

Open your VBA editor and enter the following code:

Sub replace_text_formatting()

Dim book1 As Word.Application
Dim sheet1 As Word.Document

Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("D:\SOFTEKO\List.docm")

sheet1.Content.Find.ClearFormatting
sheet1.Content.Find.Replacement.ClearFormatting

With sheet1.Content.Find
 .Text = "Mobile"
With .Replacement
    .Font.Bold = True
    .Text = "Smartphone"
 End With
 .Forward = True
 .Wrap = wdFindContinue
 .Format = True
 .MatchCase = False
 .MatchWholeWord = True
 .Execute Replace:=wdReplaceAll
End With

End Sub

Breakdown of the VBA Code

Here, the book1 is stored as a Word application.
Sheet1 is stored as a Word document.
Visible = True: It will make sure that our Word document is visible.
Documents.Open(“D:\SOFTEKO\List.docm”): It will open your Word document. Make sure to change the file location to match your device.
Content.Find.ClearFormatting: it will clear the existing formatting in the Find operation.
With sheet1.Content.Find: It will start to find the content from the document. Utilizing the WITH statement, we will attempt to avoid the repetition of typing the object name sheet1.Content.Find.
.Text = “Mobile”: Searches for the text “Mobile”.
.Font.Bold = True: It will make the replacement text’s font bold.
.Text = “Smartphone”: This is our replacement text.
.Forward = True: By setting this to True, we are searching in a forwarding manner.
.MatchCase = False: We are not making our search case-sensitive. It will search for the text, whether it is in capital letters or small letters.
.MatchWholeWord = True: It will search this as an entire text, not as part of a string.
.Wrap = wdFindContinue: It will continue to search till the end of the document.
.Execute Replace:=wdReplaceAll: Finally, it will replace all the texts.

Run the code. It will open your Word document. You will see the following result:

Method 3 – VBA to Open a Word Document and Replace Text from a Specific Paragraph

Take a look at the following Word document:

We have two paragraphs here. We will replace the text “App” with “Application,” but we will do that only for the first paragraph. Let’s get into the steps.

Steps:

Make sure the Word document is in “.docm” format.
In your VBA editor, go to Tools > References.

Enable the option “Microsoft Word 16.0 Object Library” and click OK.

Open your VBA editor and enter the following code:

Sub replace_text_paragraph()

Dim book1 As Word.Application
Dim sheet1 As Word.Document

Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("D:\SOFTEKO\Paragraph.docm")

With sheet1.Content.Paragraphs(1).Range.Find
        .Text = "App"
        .Replacement.Text = "Application"
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchCase = False
        .Execute Replace:=wdReplaceAll
End With

End Sub

Breakdown of the VBA Code

Here, book1 is stored as a Word application.
Sheet1 is stored as a Word document.
Visible = True: It will make sure that our Word document is visible.
Documents.Open(“D:\SOFTEKO\List.docm”): It will open your Word document. Make sure to change the file location to match your device.
With sheet1.Content.Paragraphs(1).Range.Find: It will start to find the text only in the first paragraph. Paragraphs(1) means the first paragraph of the Word document.
.Text = “App”: Searches for the text “App”.
.Replacement.Text = “Application”: Replace the text with “Application”.
.Wrap = wdFindStop: It will not continue to search and replace till the end of the document.
.MatchCase = False: We are not making our search case-sensitive. It will search for the text, whether it is in capital letters or small letters.
.Execute Replace:=wdReplaceAll: Finally, it will replace all the texts.

Run the code. It will open your Word document.
You will see the following result:

Method 4 – Taking User Input to Replace a Text from a Word Document with Excel VBA

Steps:

Make sure the Word document is in “.docm” format.
In your VBA editor, go to Tools > References.

Enable the option “Microsoft Word 16.0 Object Library” and click OK.

Open your VBA editor and enter the following code:

Sub replace_text_user_input()

Dim book1 As Word.Application
Dim sheet1 As Word.Document
Dim old_text As String
Dim new_text As String

Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("D:\SOFTEKO\List.docm")

old_text = InputBox("Enter the Text You want to Replace:")
new_text = InputBox("Enter the New Text:")

With sheet1.Content.Find
        .Text = old_text
        .Replacement.Text = new_text
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .Execute Replace:=wdReplaceAll
End With

End Sub

Breakdown of the VBA Code

Here, the book1 is stored as a Word application.
Sheet1 is stored as a Word document.
Visible = True: It will make sure that our Word document is visible.
Documents.Open(“D:\SOFTEKO\List.docm”): It will open your Word document. Make sure to change the file location to match your device.
old_text = InputBox(“Enter the Text You want to Replace:”): It will take the user input you want to replace and store into old_text
new_text = InputBox(“Enter the New Text:”): It will take the anther user input for your new text and stores into new_text
With sheet1.Content.Find: It will start to find the content from the document. Utilizing the WITH statement, we will attempt to avoid the repetition of typing the object name Content.Find.
.Text = old_text: Searches for your desired text.
.Replacement.Text = new_text: Replace the text with your new text.
.Forward = True: By setting this to True, we are searching in a forwarding manner.
.Wrap = wdFindContinue: It will continue to search and replace till the end of the document.
.MatchCase = False: We are not making our search case-sensitive. It will search for the text, whether it is in capital letters or small letters.
.Execute Replace:=wdReplaceAll: Finally, it will replace all the texts.

Run the code. It will open your Word document.
Enter the text you want to replace.

Enter your new text. Click OK.

You will see the following result:

Method 5 – Replace Multiple Texts from a Word Document Using VBA

Steps:

Make sure the Word document is in “.docm” format.
In your VBA editor, go to Tools > References.

Enable the option “Microsoft Word 16.0 Object Library” and click OK.

Open your VBA editor and enter the following code:

Sub open_word_replace_multiple_texts()

Dim book1 As Word.Application
Dim sheet1 As Word.Document
Dim j As Integer
Dim oldstring, newstring As String
Dim oldstringArr, newstringArr As Variant

Set book1 = CreateObject("word.application")
Set sheet1 = book1.Documents.Open("D:\SOFTEKO\List.docm")
book1.Visible = True

oldstring = InputBox("Write the text strings to be replaced ")
newstring = InputBox("Write the names of the new text strings")

oldstringArr = Split(oldstring, ",")
newstringArr = Split(newstring, ",")

If UBound(oldstringArr) <> UBound(newstringArr) Then
MsgBox "The number of text strings to find and replace must be equal"
Exit Sub
End If

For j = 0 To UBound(oldstringArr)
      With sheet1.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = oldstringArr(j)
            .Replacement.Text = newstringArr(j)
            .Format = False
            .Execute Replace:=wdReplaceAll
       End With
Next
Application.ScreenUpdating = True

End Sub

Breakdown of the VBA Code

Here, the book1 is stored as a Word application.
Sheet1 is stored as a Word document.
Visible = True: It will make sure that our Word document is visible.
Documents.Open(“D:\SOFTEKO\List.docm”): It will open your Word document. Make sure to change the file location to match your device.
oldstring = InputBox(“Write the text strings to be replaced “): It will take the user inputs you want to replace and store them into oldstring
newstring = InputBox(“Write the names of the new text strings”): It will take the other user input for your new texts and stores into newstring
oldstringArr = Split(oldstring, “,”): It is used for separating the given old strings with commas and then storing them.
newstringArr = Split(newstring, “,”): Similarly, it will separate the new strings with comma.
If UBound(oldstringArr) <> UBound(newstringArr): If the upper limit of these two arrays is not equal, then the following operation will not continue and a Message Box will pop up notifying this issue.
For j = 0 To UBound(oldstringArr): The FOR loop will perform the execution of the replacement for the limit of the array from 0 to the upper limit.
With sheet1.Content.Find: It will start to find the content from the document. Utilizing the WITH statement, we will attempt to avoid the repetition of typing the object name Content.Find.
.Text = oldstringArr(j): Searches for your desired texts.
.Replacement.Text = newstringArr(j): Replace the text with your new text.
.Execute Replace:=wdReplaceAll: Finally, it will replace all the texts.

Run the code. It will open your Word document.
Type the products you want to replace.

Type the new texts that you want in your Word document. Click OK.

You will see the following result:

Method 6 – Replace Multiple Texts from a Word Document Using a Range of Cells

Steps:

Make sure the Word document is in “.docm” format.
In your VBA editor, go to Tools > References.

Enable the option “Microsoft Word 16.0 Object Library” and click OK.

Open your VBA editor and enter the following code:

Sub replace_texts_range_of_cells()

Dim book1 As Word.Application
Dim sheet1 As Word.Document
Dim cell As Range
Dim rng As Range

Set rng = Range("B5:B8")
Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("D:\SOFTEKO\List.docm")

For Each cell In rng
With sheet1.Content.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .MatchWildcards = False
        .Wrap = wdFindContinue
        .Text = cell.Value
        .Replacement.Text = cell.Offset(0, 1)
        .Execute Replace:=wdReplaceAll
End With
Next

End Sub

Breakdown of the VBA Code

Here, the book1 is stored as a Word application.
Sheet1 is stored as a Word document.
Set rng = Range(“B5:B8”): Here, we are setting the values we want to replace.
Visible = True: It will make sure that our Word document is visible.
Documents.Open(“D:\SOFTEKO\List.docm”): It will open your Word document. Make sure to change the file location to match your device.
For Each cell In rng: The FOR loop will perform the execution of the replacement for each cell in the range that we gave earlier.
With sheet1.Content.Find: It will start to find the content from the document. Utilizing the WITH statement, we will attempt to avoid the repetition of typing the object name Content.Find.
.Wrap = wdFindContinue: It continues to search till the end of the document.
.Text = cell.Value: Takes the cell value of the left side and searches for it.
.Replacement.Text = cell.Offset(0, 1): If found, it replaces the text with the adjacent cell value.
.Execute Replace:=wdReplaceAll: Finally, it will replace all the texts.