时间戳

首页 > 解决方案 > 通过 Excel VBA，打开 Word 文档以完成邮件合并。有没有办法自动设置 Word Doc Source 等于 Excel 电子表格？

excel - 通过 Excel VBA，打开 Word 文档以完成邮件合并。有没有办法自动设置 Word Doc Source 等于 Excel 电子表格？

问题描述

有以下 Excel VBA 代码：

  1. 创建 Word 对象，因为我想将电子表格链接到 Word Mailmerge 模板
  2. 要求用户指定 Word 模板
  3. 打开 Word 模板（稍后未显示的代码将数据与模板合并到该模板中）

挑战：如果 Word 模板已经“来源”到不同的电子表格（比如上个月的电子表格名称不同），用户必须通过这些步骤来选择我称之为 Word 的“较新”电子表格模板来自。

问题：Excel VBA（或其他）中有没有办法打开word doc，以便将源更改为当前电子表格？

Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
    Set wd = CreateObject("Word.Application")
End If

Dim Letter_File As FileDialog
Set Letter_File = Application.FileDialog(msoFileDialogFilePicker)
With Letter_File
    .Filters.Clear
    .AllowMultiSelect = False
    .InitialFileName = "\\Nasprod-5\gbop\Customer Service\RMDs\Process Improvements\"
    If .Show = -1 Then
        Fileaddress = .SelectedItems(1)
    Else
        Exit Sub
    End If
End With

If MsgBox(Fileaddress, vbOKCancel) = vbCancel Then
    MsgBox ("Canceled")
    Exit Sub
End If

Set wdocSource = wd.Documents.Open(Fileaddress)

标签： excelvbams-wordmailmerge

解决方案

The following is code I use in Word vba.

Dim strFileName As String
strFileName = WorkGroupPath & "Parts\Merge Data\Clients_Merge.xlsm"
'
'   Attach Merge list
With ActiveDocument.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=strFileName, _
                    SQLStatement:="SELECT * FROM `Clients$`"    ', _
                                                                '
    '       Show merge data
    .ViewMailMergeFieldCodes = False
End With

'   Find client
Application.Dialogs(wdDialogMailMergeFindRecipient).Show

This is run upon opening/creating the primary merge document. You could assign the long name of your data source to strFilename.

You might have to change the With to:

With wd.ActiveDocument.MailMerge

The following came from recording a MailMerge in Word.

    .OpenDataSource Name:=strFileName, _
                    SQLStatement:="SELECT * FROM `Clients$`"    ', _

推荐阅读