Replace word in ms word with cell value in excel vba
19,308
Replace this line
With objWord.Selection.Find
with
With objWord.ActiveDocument.Content.Find
Option Explicit
Public Sub WordFindAndReplace()
Dim ws As Worksheet, msWord As Object
Set ws = ActiveSheet
Set msWord = CreateObject("Word.Application")
With msWord
.Visible = True
.Documents.Open "F:\Test folder\TestFolder\Test.docx"
.Activate
With .ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "CName"
.Replacement.Text = ws.Range("C1525").Value2
.Forward = True
.Wrap = 1 'wdFindContinue (WdFindWrap Enumeration)
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=2 'wdReplaceAll (WdReplace Enumeration)
End With
.Quit SaveChanges:=True
End With
End Sub
.
If column A had a list of words to be replaced, and column B - the replacements (same row):
Option Explicit
Public Sub WordFindAndReplace()
Dim ws As Worksheet, msWord As Object, itm As Range
Set ws = ActiveSheet
Set msWord = CreateObject("Word.Application")
With msWord
.Visible = True
.Documents.Open "F:\Test folder\TestFolder\Test.docx"
.Activate
With .ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting
For Each itm In ws.UsedRange.Columns("A").Cells
.Text = itm.Value2 'Find all strings in col A
.Replacement.Text = itm.Offset(, 1).Value2 'Replacements from col B
.MatchCase = False
.MatchWholeWord = False
.Execute Replace:=2 'wdReplaceAll (WdReplace Enumeration)
Next
End With
.Quit SaveChanges:=True
End With
End Sub
.
Find Reference - Criteria for find operations
Methods
Name Description
'-----------------------------------------------------------------------------------------
ClearAllFuzzyOptions Clears all nonspecific search options for Japanese text
ClearFormatting Removes text and paragraph formatting from the text
ClearHitHighlight Removes highlighting for all text. Boolean (Successful/Not)
Execute Runs the find operation. Boolean (Successful/Not)
Execute2007 Runs the find operation. Boolean (Successful/Not)
HitHighlight Highlights all found matches. Boolean (Successful/Not)
SetAllFuzzyOptions Activates all nonspecific search options for Japanese text
Properties - 1 of 2
Name Description
'-----------------------------------------------------------------------------------------
Application Returns an Application object that represents the Ms Word app
CorrectHangulEndings Read/Write Boolean - True if it corrects Hangul endings
Creator Read-only Long - Returns 32-bit int - indicates app of the object
Font Read/Write Font - Returns or sets a Font object (char formatting)
Format Read/Write Boolean - True if formatting is included
Forward Read/Write Boolean - True if the find operation searches forward
Found Read-only Boolean - True if the search produces a match
Frame Read-only - formatting for specified style or find/replace
HanjaPhoneticHangul Read/Write Boolean - locate phonetic Hangul & hanja chars in Korean
Highlight Read/Write Long - True if highlight formatting included in criteria
IgnorePunct Read/Write Boolean - ignore punctuation in found text
IgnoreSpace Read/Write Boolean - ignore extra white space in found text
LanguageID Read/Write WdLanguageID - Returns or sets the language
LanguageIDFarEast Read/Write WdLanguageID - Returns or sets an East Asian language
LanguageIDOther Read/Write WdLanguageID - Returns or sets the language
MatchAlefHamza Read/Write Boolean - True if find match txt with alef hamzas Arabic
MatchAllWordForms Read/Write Boolean - True for all forms ("sit," "sat" and "sitting")
MatchByte Read/Write Boolean - True if distinguishes full or half-width ltrs
MatchCase Read/Write Boolean - True if it is case sensitive. Default is False
MatchControl Read/Write Boolean - True for right-to-left lang
MatchDiacritics Read/Write Boolean - True for right-to-left lang
MatchFuzzy Read/Write Boolean - True if uses nonspecific options for Japanese
MatchKashida Read/Write Boolean - True for matching kashidas in an Arabic
MatchPhrase Read/Write Boolean - True ignores white sp/ctrl chars between words
MatchPrefix Read/Write Boolean - True to match words beginning with search str
MatchSoundsLike Read/Write Boolean - True to return words that sound similar
MatchSuffix Read/Write Boolean - True to match words ending with search str
MatchWholeWord Read/Write Boolean - True to locate only entire words
MatchWildcards Read/Write Boolean - True if the text to find contains wildcards
.
Properties - 2 of 2
Name Description
'-----------------------------------------------------------------------------------------
NoProofing Read/Write Long - True to find/replace txt ignored by spell & grammar
ParagraphFormat Returns or sets a ParagraphFormat object (settings). Read/write
Parent Returns parent object of the specified Find object
Replacement Returns Replacement object that contains criteria for replace op
Style Read/Write Variant - Returns or sets style for the specified object
Text Read/Write String - Returns or sets the text to find
Wrap Read/write WdFindWrap - wrapping if start point other than doc start
Related videos on Youtube
Comments
-
Eric over 1 year
I've been experimenting with the code below but I can't make it work. What I'm trying to accomplish is for example cell "A1" in excel has a customers name, and I want to replace every instance that has "CName" in a word document with the value in cell "A1". Currently, the code only selects "CName" in the word document but it does not replace the value.
Sub test() Dim ws As Worksheet Dim objWord As Object Dim i As Integer Dim strValue As String Set ws = ThisWorkbook.Sheets("CustomerNames") Set objWord = CreateObject("Word.Application") objWord.Visible = True objWord.Documents.Open "F:\Test folder\TestFolder\Test.docx" objWord.Activate strValue = Range("C1525").Value With objWord.Selection.Find .Text = "CName" .Replacement.Text = strValue .Execute Replace:=wdReplaceAll End With End Sub
-
Christopher Oezbek over 3 yearsFor a more general solution to this, I have written a generic script that will export all Named Ranges (using the Name Box) to Word: github.com/coezbek/…
-
-
MathCurious314 over 2 yearsThe second code only works for the content of the body but not for the header/footer. How can one make it work for the header?