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.


Download Practice Workbook

Download the practice workbook and word documents below.


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.

excel vba open word document and replace text

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

excel vba open word document and replace text

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.

Before you run the code, make sure the word document is in “.docm” format. And also you need an extra step to execute the code through the word document.

First, in your VBA editor, go to Tools > References.

Then, enable the option “Microsoft Word 16.0 Object Library” and click OK.

excel vba open word document and replace text

📌 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

🔎 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 according to 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.
  • 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: Excel VBA: How to Find and Replace Text in Word Document


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.

excel vba open word document and replace text

Before you run the code, make sure the word document is in “.docm” format. And also you need an extra step to execute the code through the word document.

First, in your VBA editor, go to Tools > References.

Then, enable the option “Microsoft Word 16.0 Object Library” and click OK.

excel vba open word document and replace text

📌 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

🔎 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 according to 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 font Bold of the replacement text.
  • .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 or small letter.
  • .MatchWholeWord = True: It will search this as an entire text not a 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.
  • 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: How to Replace Text in Excel Formula (7 Easy Ways)


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.

Before you run the code, make sure the word document is in “.docm” format. And also you need an extra step to execute the code through the word document.

First, in your VBA editor, go to Tools > References.

Then, enable the option “Microsoft Word 16.0 Object Library” and click OK.

excel vba open word document and replace text

📌 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

🔎 Breakdown of the VBA Code

  • Here, book1 is stored as Word application.
  • Sheet1 is stored as 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 according to 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 or small letter.
  • .Execute Replace:=wdReplaceAll: Finally, it will replace all the texts.
  • Now, run the code. After that, it will open your word document.
  • Then, you will see the following result:

excel vba open word document and replace text

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 Replace Text after Specific Character in Excel (3 Methods)


Similar Readings


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.

excel vba open word document and replace text

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

Before you run the code, make sure the word document is in “.docm” format. And also you need an extra step to execute the code through the word document.

First, in your VBA editor, go to Tools > References.

Then, enable the option “Microsoft Word 16.0 Object Library” and click OK.

excel vba open word document and replace text

📌 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

🔎 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 according to 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 or small letter.
  • .Execute Replace:=wdReplaceAll: Finally, it will replace all the texts.
  • 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.

excel vba open word document and replace text

  • 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: Excel VBA to Find and Replace Text in a Column (2 Examples)


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.

excel vba open word document and replace text

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

Before you run the code, make sure the word document is in “.docm” format. And also you need an extra step to execute the code through the word document.

First, in your VBA editor, go to Tools > References.

Then, enable the option “Microsoft Word 16.0 Object Library” and click OK.

excel vba open word document and replace text

📌 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

🔎 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 according to your device.
  • oldstring = InputBox(“Write the text strings to be replaced “): It will take the user inputs you want to replace and stores them into oldstring
  • newstring = InputBox(“Write the names of the new text strings”): It will take the anther 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.
  • Now, run the code. After that, it will open your word document.
  • Next, type the products you want to replace.

excel vba open word document and replace text

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

  • Then, you will see the following result:

excel vba open word document and replace text

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

Read More: How to Replace Text in Selected Cells in Excel (4 Simple Methods)


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:

excel vba open word document and replace text

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.

Before you run the code, make sure the word document is in “.docm” format. And also you need an extra step to execute the code through the word document.

First, in your VBA editor, go to Tools > References.

Then, enable the option “Microsoft Word 16.0 Object Library” and click OK.

excel vba open word document and replace text

📌 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

🔎 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 according to 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 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.
  • Now, run the code. After that, it will open your word document. And you will see the following output:

excel vba open word document and replace text

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

Read More: Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)


💬 Things to Remember

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

✎ Change the file location according to your device.


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo