If you are looking for some of the effective and easiest ways to find and replace text in a Word document using Excel VBA then you will find this article useful.
So, let’s get into the main article.
Excel VBA Find and Replace Text in Word Document: 5 Ways
Here, we will demonstrate the ways of replacing a specific text or a range of text strings from a word document containing a range of text strings with the help of Excel VBA.
For this purpose, we will consider various conditions like finding and replacing a single text, replacing a text with formatting, replacing all of the defined texts from the first paragraph moreover replacing a range of text strings at a time.
We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.
Method-1: Find and Replace a Text String in Word Document Using Excel VBA
Here, we have the following Word document containing the list of some products and we have saved the file name as Product.docm which we will use in the VBA code.
Our task is here to replace the text Apple from the list with Cherry using a VBA code.
Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.
Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.
After that, a Module will be created.
We have to do an extra step to execute the code through the Word document.
➤ Go to the Tools Tab >> References Option.
Then, the References – VBAProject wizard will open up.
➤ Enable the Microsoft Word 16.0 Object Library option, and press OK.
Step-02:
➤ Write the following code
Sub findandreplaceword1()
Dim book1 As Word.Application
Dim sheet1 As Word.Document
Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("C:\Users\Mima\Downloads\Product.docm")
With sheet1.Content.Find
.Text = "Apple"
.Replacement.Text = "Cherry"
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End SubHere, we have declared the book1 as Word.Application object and sheet1 as Word.Document object, then book1 is set to the Word.Application and sheet1 to the path of the document which we want to open.
Using the WITH statement we will try to avoid the repetition of typing the object name sheet1.Content.Find and here we will find the text string “Apple” and replace it with the string “Cherry”.
.Wrap = wdFindContinue is for continuing the operation till the end of the word document, and .Execute Replace:=wdReplaceAll is for replacing all of the strings.
➤ Press F5.
Then, you will get Cherry in the place of the Apple in the Product List of the Word document.
Method 2: Find and Replace a Text String in a Word Document with Change of Formatting
In this section, we will replace the text string Apple with Cherry, and then, we will bold the font style of the replaced text Cherry with the help of a VBA code.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub findandreplaceword2()
Dim book1 As Word.Application
Dim sheet1 As Word.Document
Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("C:\Users\Mima\Downloads\Product.docm")
sheet1.Content.Find.ClearFormatting
sheet1.Content.Find.Replacement.ClearFormatting
With sheet1.Content.Find
.Text = "Apple"
With .Replacement
.Font.Bold = True
.Text = "Cherry"
End With
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
End SubHere, we have declared book1 as Word.Application object and sheet1 as Word.Document object, then book1 is set to the Word.Application and sheet1 to the path of the document which we want to open.
Using the WITH statement we will try to avoid the repetition of typing the object name sheet1.Content.Find. And, here we will find the text string “Apple” and replace it with the string “Cherry” and .Font.Bold = True to make this string bold.
.Wrap = wdFindContinue is for continuing the operation till the end of the word document. .Format = True for changing the format also, and .Execute Replace:=wdReplaceAll is for replacing all of the strings.
➤ Press F5.
After that, you will get a bold font string Cherry in the place of the Apple in the Product List of the Word document.
Method-3: Find and Replace Text Strings from a Certain Paragraph in Word Document
Here, we will be finding the text string app throughout the first paragraph of the following passage. And then, replace it with the string Application with the help of a VBA code.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub findandreplaceword3()
Dim book1 As Word.Application
Dim sheet1 As Word.Document
Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("C:\Users\Mima\Downloads\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 SubHere, we have declared book1 as Word.Application object and sheet1 as Word.Document object, then book1 is set to the Word.Application and sheet1 to the path of the document which we want to open.
Using the WITH statement we will try to avoid the repetition of typing the object name sheet1.Content.Paragraphs(1).Range.Find. Where Paragraphs(1) is for the first paragraph and here we will find the text string “App” and replace it with the string “Application”.
.Wrap = wdFindStop is for not continuing the operation till the end of the word document. And .Execute Replace:=wdReplaceAll is for replacing all of the strings.
➤ Press F5.
Finally, you will get the text string Application in all of the places in the first paragraph instead of the app in the Word document. But the text will not be replaced in the second paragraph.
Method-4: Find and Replace a Range of Text Strings in Word Document with the Help of InputBox
Here, we will replace the texts Orange, Guava, Broccoli, and Potatoes at the same time with the help of a VBA code using the InputBox function.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub findandreplaceword4()
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("C:\Users\Mima\Downloads\Product.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 SubHere, we have declared book1 as Word.Application object and sheet1 as Word.Document object, j as Integer, oldstring, newstring as String and oldstringArr, newstringArr as Variant.
Then book1 is set to the Word.Application and sheet1 to the path of the document which we want to open.
After that, two input boxes will appear for taking the inputs from the user; one is for the old strings which we have assigned to oldstring and the other one is for the new strings and we have assigned them to newstring.
oldstringArr = Split(oldstring, “,”) is used for separating the given old strings with commas and then storing them and similarly, newstringArr will store the new strings as an array and when the upper limit of these two arrays will not be equal then the following operation will not continue and a Message Box (MsgBox) will pop up notifying this issue.
The FOR loop will execute the operation of the replacement for the limit of the array from 0 to the upper limit.
Now, it is time to run our code.
➤ Press F5.
Then, the first input box will open up, and here type the name of the old strings; Guava, Broccoli, Potatoes, Orange, and press OK.
After that, you will get another input box for the new text strings.
➤ Type the name of the new strings; Clementine, Cabbage, Okra, Eggplant, and press OK.
Finally, you will have the new text strings Clementine, Cabbage, Okra, Eggplant in the place of the old strings Guava, Broccoli, Potatoes, Orange in the Word document.
Method-5: Find and Replace a Range of Text Strings in Word Document
In this section, we will try to find out a range of text strings in the Word document. And then, replace them with our desired strings with the help of a VBA code.
For this purpose, we have listed them in a worksheet of Excel like the following figure.
Here, the old strings are Guava, Broccoli, Potatoes, Orange, and their corresponding new ones are Clementine, Cabbage, Okra, Eggplant.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub findandreplaceword5()
Dim book1 As Word.Application
Dim sheet1 As Word.Document
Dim i As Integer
Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("C:\Users\Mima\Downloads\Product.docm")
For i = 4 To 7
With sheet1.Content.Find
.ClearFormatting
.Replacement.ClearFormatting
.MatchWildcards = False
.Wrap = wdFindContinue
.Text = Cells(i, 2)
.Replacement.Text = Cells(i, 3)
.Execute Replace:=wdReplaceAll
End With
Next i
End SubHere, we have declared book1 as Word.Application object and sheet1 as Word.Document object, j as Integer.
Then book1 is set to the Word.Application and sheet1 to the path of the document which we want to open.
The FOR loop will execute the operation for rows 4 to 7 of the Excel worksheet.
.Text = Cells(i, 2) is the value of the old strings in Column B. And .Replacement.Text = Cells(i, 3) is the value of the new strings in Column C.
➤ Press F5.
Eventually, you will get the new text strings Clementine, Cabbage, Okra, Eggplant in the place of the old strings Guava, Broccoli, Potatoes, Orange in the Word document.
Download Workbook
Conclusion
In this article, we tried to cover the ways to find and replace text in a Word document with the help of Excel VBA. I hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
Related Articles
- How to Find and Replace from List with Macro in Excel
- Excel VBA to Find and Replace Text in a Column
- How to Find and Replace Multiple Values in Excel with VBA
- Find and Replace a Text in a Range with Excel VBA
- Excel VBA to Replace Blank Cells with Text
- Excel VBA: Replace Character in String by Position
- Excel VBA: How to Replace Text in String
