☰

使用 excel vba 中的单元格值替换 ms word 中的单词

tag-icon • tag-icon microsoft-excel microsoft-word vba macros
使用 excel vba 中的单元格值替换 ms word 中的单词

我一直在尝试下面的代码，但无法让它工作。我试图实现的是，例如，excel 中的单元格“A1”有一个客户姓名，我想用单元格“A1”中的值替换 word 文档中所有包含“CName”的实例。目前，代码只在 word 文档中选择“CName”，但不会替换该值。

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

答案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

相关内容