VBA基于Excel中的数据库在单词中查找和替换 [英] VBA Find and Replace in word based on Database in Excel

问题描述

我正在寻找一种在Excel中根据Excel中的数据查找和替换Word文档中的文本的方法。

更具体地说：

列A =文本我想查找
列B =文本我要用

替换找到的文本

基本查找和替换VBA中的代码，但我努力工作如何做这样的（跨应用程序）。

任何人都可以帮助我？ p>

感谢：）

REQUESTED SCREENSHOT：

img src =https://i.stack.imgur.com/4gxEb.pngalt =enter image description here>

我剪下了一个范例

解决方案

以下代码需要引用

code> MS Word 14.0对象库或者什么是等效的在你的结尾。如果您不想早期绑定，则由您将其转换为晚期绑定。

  Sub FindReplaceInWord 
 $ b Dim Wbk As Workbook：Set Wbk = ThisWorkbook 
 Dim Wrd As New Word.Application 
 Dim Dict As Object 
 Dim RefList As Range，RefElem As Range 
 
 Wrd.Visible = True 
 Dim WDoc As Document 
设置WDoc = Wrd.Documents.Open（C：\xxx\Doc1.docx）根据需要修改。 
 
 Set Dict = CreateObject（Scripting.Dictionary）
设置RefList = Wbk.Sheets（Sheet1）。范围（A1：A3）'根据需要修改。 
 
 With Dict 
对于RefList中的每个RefElem 
如果不是.Exists（RefElem）和Not IsEmpty（RefElem）则
 .Add RefElem.Value，RefElem.Offset （0，1）.Value 
结束如果
下一个RefElem 
结束于
 
对于每个键在词典
使用WDoc.Content.Find 
 .Execute FindText：= Key，ReplaceWith：= Dict（Key）
 End With 
 Next Key 
 
如果需要，启用以下三个。 
'WDoc.Save 
'WDoc.Close 
'Wrd.Quit 
 
 End Sub 
  

屏幕截图：

Word文档运行代码）

Excel中的参考列表 p>

>

Word文档（运行代码之后）

让我们知道这是否

为了参考，代码没有您的列表的问题。我认为这是别的东西。查看我的屏幕截图。

/ p>

正如你所看到的，使用 Debug.Print Key，Dict（Key）显示它是安全的。我修改了上面的代码来改变可能导致这种情况的东西。 ：）

I am looking for a way to Find and Replace text in a Word Document based on Data in Excel in excel

More specifically:

Column A = Text I want to find Column B = Text I want to replace the found text with

I already know basic Find and Replace code in VBA, but I'm struggling to work out how to do it like this (Cross-application).

Can anyone help me?

Thanks :)

REQUESTED SCREENSHOT:

I've clipped a sample of my spreadsheet due to potentially sensitive information being displayed but this gives the jist.

解决方案

The following code needs the reference to the MS Word 14.0 Object Library or whatever is the equivalent on your end. If you don't want early binding, it's up to you to convert it to late binding.

Sub FindReplaceInWord()

    Dim Wbk As Workbook: Set Wbk = ThisWorkbook
    Dim Wrd As New Word.Application
    Dim Dict As Object
    Dim RefList As Range, RefElem As Range

    Wrd.Visible = True
    Dim WDoc As Document
    Set WDoc = Wrd.Documents.Open("C:\xxx\Doc1.docx") 'Modify as necessary.

    Set Dict = CreateObject("Scripting.Dictionary")
    Set RefList = Wbk.Sheets("Sheet1").Range("A1:A3") 'Modify as necessary.

    With Dict
        For Each RefElem In RefList
            If Not .Exists(RefElem) And Not IsEmpty(RefElem) Then
                .Add RefElem.Value, RefElem.Offset(0, 1).Value
            End If
        Next RefElem
    End With

    For Each Key In Dict
        With WDoc.Content.Find
            .Execute FindText:=Key, ReplaceWith:=Dict(Key)
        End With
    Next Key

    'Enable the following three if you want.
    'WDoc.Save
    'WDoc.Close
    'Wrd.Quit

End Sub

Screenshots:

Word Document (before running code)

Reference List in Excel

Word Document (after running code)

Let us know if this helps.

EDIT:

For reference, the code doesn't have problems with your list. I think it's something else altogether. See my screenshot.

As you can see, using Debug.Print Key, Dict(Key) shows it's safe. I have modified the code above to change something that might be causing this. :)

这篇关于VBA基于Excel中的数据库在单词中查找和替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！