Excel VBA: Open Word Document and Replace Text (6 Examples)

Using Microsoft Excel VBA, we can easily find and replace text(s) from a sheet. It saves us a lot of time and reduces stress. But is it possible to replace text from a word document using VBA? Well, obviously, it is. If you want to open a word document and replace text using VBA in Excel, you have come to the right place. This article will teach you exactly that.

This article will be on point with suitable examples and proper illustrations. So, read the whole article to improve your knowledge.

6 Examples to Open Word Document and Replace Text with Excel VBA

In the following sections, we will provide you with six examples to open a word document and replace text using VBA in Excel. We recommend you learn and apply all these methods. It will surely develop your Excel knowledge. Let’s get into it.

Now, some things to remember:

We saved our word document in “.docm” format.

First, you need to open your VBA editor by pressing Alt+F11 on your keyboard. After that, click on Insert > Module. After that, you can write your codes.

1. Open Word Document and Replace a Text with Excel VBA

Now, we will open a word document and replace text with Excel VBA. This is a simple code that you can follow to start with.

Let’s check our word document. Here, our word document name is “List.docm”.

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

📌 Steps

• First, open your VBA editor and type 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

• Now, run the code. After that, it will open your word document. Then, you will see the following result:

As you can see, our VBA code in Excel worked successfully to open the word document and replace the text.

Read More: How to Generate a Word Document from an Excel Macro

2. Replace Text and Change Formatting from a Word Document

Now, from the word document, we will replace the text “Mobile” with “Smartphone” and also, we will make the text Bold.

📌 Steps

• First, open your VBA editor and type 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

• Now, run the code. After that, it will open your word document. Then, you will see the following result:

As you can see from the result, this VBA code successfully replaced text and changed the formatting of the text in the word document.

Read More: Excel VBA: Open Word Document and Paste

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

In this example, you will learn to open a word document and replace text from a specific paragraph.

Take look at the following word document:

Here, 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

• First, open your VBA editor and type 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

• Now, run the code. After that, it will open your word document.
• Then, you will see the following result:

As you can see from the result, this VBA code in Excel is successful to replace text from the specific paragraph in the word document. Also, it worked with only the first paragraph, not the second one.

Read More: How to Open Word Document and Save As PDF or Docx with VBA Excel

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

Now, you can also take user inputs to replace text from a word document using VBA in Excel.

Here, we will replace the text “Mobile” with “Smartphone” using the input box in Excel. Let’s get into it.

📌 Steps

• First, open your VBA editor and type 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

• Now, run the code. After that, it will open your word document.
• Then, enter the text you want to replace.

• Next, enter your new text. After that, click on OK.

• Then, you will see the following result:

In the end, our VBA code in Excel worked perfectly to open the word document and replace the text using our own input.

Read More: Import Data from Excel into Word Automatically Using VBA

5. Replace Multiple Texts from a Word Document Using VBA

In this example, you will learn to replace multiple texts from a word document using VBA in Excel. Here, we are also taking user inputs to perform this.

From the word document, we will replace the texts “Fridge”, “Laptop”, “Headphone” with “Monitor”, “Notebook”, and “Earbud”.

📌 Steps

• First, open your VBA editor and type 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

• Now, run the code. After that, it will open your Word document.
• Next, type the products you want to replace.

• Then, type the new texts that you want in your word document. After that, click on OK.

• Then, you will see the following result:

As you can, we successfully used the VBA code in Excel to open the word document and replaced multiple texts.

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

In this example, you will learn to open a word document and replace text based on a range in Excel. Basically, we are going to replace the following texts with these new ones:

Here, we will use the range of cells B5:B8 to search for the texts and we will replace them with the adjacent cell values.

📌 Steps

• First, open your VBA editor and type 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

• Now, run the code. After that, it will open your word document. And you will see the following output:

As you can see, our VBA code in Excel worked perfectly to open a word document and replace text from it.

💬 Things to Remember

✎ Make sure to save your word document in “.docm” format.

✎ Change the file location according to your device.

Download Practice Workbook

Download the practice workbook and word documents below.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to open a word document and replace text in Excel using VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Keep learning new methods and keep growing!