Заменить слово в мс слово на значение ячейки в Excel VBA

Question

Я экспериментировал с приведенным ниже кодом, но не могу заставить его работать. Я пытаюсь выполнить, например, ячейку "А1" в Excel с именем клиента, и я хочу заменить каждый экземпляр, имеющий "CName" в текстовом документе, значением в ячейке "А1". В настоящее время код выбирает только "CName" в документе word, но не заменяет значение.

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

Замени эту строку

• With objWord.Selection.Find

с

• 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

---

,

Если в столбце A был список заменяемых слов, а в столбце B - замены (та же строка):

---

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

---

,

Найти ссылку - Критерии для операций поиска

---

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