☰

如何将 Excel 工作簿中具有相同邮件合并字段的多个文档邮件合并到 Word 中？

tag-icon • tag-icon microsoft-excel microsoft-word mail-merge
如何将 Excel 工作簿中具有相同邮件合并字段的多个文档邮件合并到 Word 中？

在我用来创建 PDF 的多个文档中，每个文档上都会看到相同的合并字段。

我有从 Excel 工作簿读取到 Word 文档的邮件合并字段，并在 Word 文档中提取了以下列。

  • Name_of_account
  • Account_number
  • Effective_date_of_account_policy

在多个文档中看到这三行相同的行，我知道如何使用“预览结果”选项卡合并这些字段以查看每个单独文档的合并数据。

例如，我怎样才能一次性抓取所有这些文档，并让这三个字段从 Excel 工作簿中填充合并字段，而不必打开每个文档，用合并的数据替换它，保存，然后对打开的每个文档重复此操作。

或者，是否有一个程序，我可以将所有这些文档放入某个东西中，然后当我打开每个文档时，合并字段就会被填充？

答案1

如果您想一次性完成所有批处理，这里有两个免费的实用程序可用于批处理：

不过，我怀疑你希望一次只使用一个。我已经这样做了很多年，很高兴与大家分享我的方法。

  1. 我不使用文档，而是使用 Word模板。他们根据包含合并字段的模板创建新文档。模板不是合并文档，而是非合并文档。

  2. 该模板具有一个 AutoNew 宏，该宏在创建新文档时运行。它将新文档更改为信件合并文档并附加数据源。它设置视图以预览合并。

  3. 由于这项设置适用于我办公室的任何计算机，因此宏会根据工作组模板设置的位置识别该数据源。这样，它就适用于办公室中的每个人。

  4. 完成后可以使用一个宏，将其从合并文档更改为常规文档，并锁定合并字段以保留其内容。它附加到邮件选项卡上的一个按钮，但可以通过键盘快捷键或 QAT 按钮触发。

    功能区按钮

  5. 还有另一个宏可以重新连接数据源并重新激活合并字段。可以使用 QAT 按钮触发此宏。

将文档设置为合并文档并附加数据的宏位于全局模板。它是从模板中的 AutoNew 宏调用的。

其他宏位于全局模板中。QAT 按钮和功能区按钮也位于全局模板中。这样，可以通过更改全局模板中的模板来对合并的所有模板进行任何修改。

以下是全局模板中的宏。

Sub AttachData()
'   Written by Charles Kenyon
'   19 April 2005 revised 15 December 2006
'       revised 12 March 2014 to clean up attachment code
'
'   Requires WorkGroupPath function
'
'   Makes activedocument a mailmerge (letter) document and
'   attaches Clients_Merge.xls from Parts folder of Workgroup Templates folder.
'
'   Then displays search dialog and goes to client, makes sure merge info is
'   displayed instead of merge codes.
'
    On Error Resume Next
'
'   SET THE FOLLOWING CONSTANTS TO REFLECT THE LOCATION OF YOUR DATA IN RELATION TO THE WORKGROUP TEMPLATES FOLDER
    Const strDATAFILE As String = "Parts\Merge Data\Clients_Merge.xlsm"
    Const strSHEET_TABLE As String = "`Clients$`"
'   THE ABOVE LIKELY WILL REQUIRE CONSIDERABLE TWEAKING
'
    Call MergeFieldUnlockAllStory 'if merge fields locked, unlock
'
'   Name of file
    Dim strFileName As String
    
    Dim strProvider As String
    strFileName = WorkGroupPath & strDATAFILE
'
'   Attach Merge list
    With ActiveDocument.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:=strFileName, _
            SQLStatement:="SELECT * FROM " & strSHEET_TABLE ', _
'
'       Show merge data
        .ViewMailMergeFieldCodes = False
    End With
'
'   Find client
    Application.Dialogs(wdDialogMailMergeFindRecipient).Show
'
    On Error GoTo 0
'
'   Disconnect from Merge - OPTIONAL - use if you are not going to add merge fields in document after creation
    Call DisconnectMerge
End Sub

Sub DisconnectMerge()
' Charles Kenyon November 20, 2020
' DisconnectMerge Macro
'
'
    Call MergeFieldLockAllStory ' locks merge fields with current content
    '   MAKE CURRENT DOCUMENT NON-MERGE - ORDINARY DOCUMENT
    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
End Sub

Sub MergeFieldLockAllStory()
'   Written by Charles Kyle Kenyon 15 November 2020
'
'   All Story Field Locker - Merge fields
    Dim oField As Field
    Dim oStory As Range
'    On Error Resume Next
    For Each oStory In ActiveDocument.StoryRanges
    ' This goes into headers and footers as well as the regular document
        Do
            For Each oField In oStory.Fields
                If oField.Type = wdFieldMergeField Then
                    oField.Locked = True
                End If
            Next oField
            Set oStory = oStory.Next
        Loop Until oStory Is Nothing
    Next oStory
    Set oStory = Nothing
    Set oField = Nothing
End Sub

Sub MergeFieldUnlockAllStory()
'   Written by Charles Kyle Kenyon 15 November 2020
'
'   All Story Field Locker - Merge fields
    Dim oField As Field
    Dim oStory As Range
'    On Error Resume Next
    For Each oStory In ActiveDocument.StoryRanges
    ' This goes into headers and footers as well as the regular document
        Do
            For Each oField In oStory.Fields
                If oField.Type = wdFieldMergeField Then
                    oField.Locked = False
                End If
            Next oField
            Set oStory = oStory.Next
        Loop Until oStory Is Nothing
    Next oStory
    Set oStory = Nothing
    Set oField = Nothing
End Sub

Private Function WorkGroupPath() As String
'   Written by Charles Kenyon
'   February 28, 2003 Updated November 15, 2020
'
'   Used by templates menus to set location of templates.
'   Returns workgroup tempates path with "\" at the end.
'
'   This is needed because if the folder is a network drive rather
'   than a folder, it will have the "\" already. If it is a folder,
'   it will not have the backslash. This function gives a string
'   with the backslash in either case.
'
    Let WorkGroupPath = Application.Options.DefaultFilePath(wdWorkgroupTemplatesPath)
    If Right(WorkGroupPath, 1) <> "\" Then
        Let WorkGroupPath = WorkGroupPath & "\"
    End If
End Function

这是进入文档模板的宏，它具有合并字段，但本身不是合并文档。

Sub AutoNew()
' Charles Kenyon November 20, 2020
     Application.Run AttachData
End Sub

这里有一个链接到模板其中包含此代码和更多内容。我期望稍后对其进行调整，以添加用于宏的 QAT 按钮和功能区按钮。

相关内容