Substituir palavra em ms com valor de célula no excel vba

Question

Estou experimentando o código abaixo, mas não consigo fazer isso funcionar. O que estou tentando realizar é, por exemplo, a célula "A1" no excel ter um nome de cliente e quero substituir cada instância que tenha "CName" em um documento do Word com o valor na célula "A1". Atualmente, o código seleciona apenas "CName" no documento do word, mas não substitui o valor.

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

    

Answer 1

Substitua esta linha

• With objWord.Selection.Find

com

• 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

.

Se a coluna A tiver uma lista de palavras a serem substituídas e a coluna B - as substituições (mesma linha):

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