VBA 宏：Excel 到 Word 文本替换

VBA macros: Excel to Word text replacement

提问by potapuff

There is simple VBA macros for Excel (2003). It look to cell A$N and B$N, and replace in Word document text from B$N to A$N.

Excel (2003) 有简单的 VBA 宏。它查找单元格 A$N 和 B$N，并将 Word 文档中的文本从 B$N 替换为 A$N。

Sub Макрос1()

Dim pathh As String, i As Integer
pathh = "c:.doc"
Dim pathhi As String
Dim from_text As String, to_text As String
Dim WA As Object, WD As Object
Set WA = CreateObject("Word.Application")
WA.Documents.Open (pathh)
WA.Visible = True

For oCell = 1 To 150
    from_text = Range("B" + CStr(oCell)).Value
    to_text = Range("A" + CStr(oCell)).Value
    With WA
        .Activate
        With .Selection.Find
          .ClearFormatting
          .Replacement.ClearFormatting

          .Text = from_text
          .Replacement.Text = to_text
          .Execute Replace:=wdReplaceAll
        End With
    End With
Next
End Sub

Problem: in Word document this script only select text, but don't make replacement. Nay suggestion?

问题：在 Word 文档中，此脚本仅选择文本，而不进行替换。不建议？

回答by K_B

First I expect you to have the reference to the Word object library active:

首先，我希望您对 Word 对象库的引用处于活动状态：

The the script whould actually work, I made some minor modifications and have this working including replace:

实际工作的脚本，我做了一些小的修改，并让这个工作包括替换：

Sub test1()

    Dim pathh As String
    Dim pathhi As String
    Dim oCell  As Integer
    Dim from_text As String, to_text As String
    Dim WA As Object

    pathh = "C:.doc"

    Set WA = CreateObject("Word.Application")
    WA.Documents.Open (pathh)
    WA.Visible = True

    For oCell = 1 To 2
        from_text = Sheet2.Range("B" & oCell).Value
        to_text = Sheet2.Range("A" & oCell).Value
        With WA
            .Activate
            With .Selection.Find
              .ClearFormatting
              .Replacement.ClearFormatting

              .Text = from_text
              .Replacement.Text = to_text
              .Execute Replace:=wdReplaceAll
            End With
        End With
    Next
End Sub

回答by MikeF

Using Excel VBA 7.0 and Word 14.0 object librarythe With .Selection block needs to be modified slightly

使用 Excel VBA 7.0 和 Word 14.0 对象库，需要稍微修改 With .Selection 块

            With .Selection.find
            .ClearFormatting
            .Execute FindText:=from_text, ReplaceWith:to_text, replace:=wdReplaceAll
        End With