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.
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.
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.
📌 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.
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.
📌 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.
📌 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:
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
Similar Readings
- Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
- How to Replace Text with Carriage Return in Excel (4 Smooth Approaches)
- Copy from Excel to Word Without Losing Formatting (4 Easy Ways)
- How to Replace Text between Two Characters in Excel (3 Easy Ways)
- How to Auto Populate Word Document from Excel (with Quick Steps)
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.
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.
📌 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.
- 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.
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.
📌 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.
- 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.
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:
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.
📌 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:
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
- Excel VBA to Replace Blank Cells with Text (3 Examples)
- How to Replace Text with Blank Cell in Excel (5 Simple Methods)
- Excel VBA: How to Replace Text in String (5 Effective Ways)
- How to Paste Excel Table into Word in Landscape (3 Easy Ways)
- Excel VBA: Replace Character in String by Position (4 Effective Ways)
- How to Copy Only Text from Excel to Word (3 Quick Methods)