vba Microsoft Word 邮件合并数据源自动定位

Microsoft Word Mail Merge Data Source auto location

提问by Ryan Gillooly

I have written an Excel > Word Mail merge where the word document is the mail merge template.

我写了一个 Excel > Word 邮件合并，其中 Word 文档是邮件合并模板。

At the moment the location of the excel file (data source) has to be hard coded.

目前，必须对 excel 文件（数据源）的位置进行硬编码。

I want to be able to move the files about the network, as long as they are in the same folder as each other, so it recognizes the data source is still the excel file.

我希望能够移动有关网络的文件，只要它们彼此在同一文件夹中，因此它识别数据源仍然是excel文件。

I currently have this piece of code which i thought would work. When the word document is opened at any point, it will re-create the data source by looking to the current directory of the file and looking for the file name PM MailMerge.xlsm.

我目前有这段我认为可以工作的代码。在任何时候打开 word 文档时，它都会通过查找文件的当前目录并查找文件名 PM MailMerge.xlsm 来重新创建数据源。

Then when it has been created, a message box should show the new mailmerge datasource.

然后当它被创建时，一个消息框应该显示新的邮件合并数据源。

This works, but then when i move both files into any other folder, it fails and says cannot find datasource.

这有效，但是当我将两个文件移动到任何其他文件夹时，它失败并说找不到数据源。

Code:

代码：

Private Sub Document_Open()

Dim strBook As String
Dim strBookName As String
Dim strDataSource As String

strBookName = "\PM MailMerge.xlsm"
strBook = ActiveDocument.Path & strBookName
strDataSource = ActiveDocument.MailMerge.DataSource.Name

ActiveDocument.MailMerge.OpenDataSource Name:= _
strBook, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=strBook;Mode=Read;Extended                       Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry       Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:D", _
SQLStatement:="SELECT * FROM `Merge$`", _
SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess

MsgBox "Current Datasource -   " + strDataSource

End Sub

回答by

It fails because Word tries to connect to the existing data source before it executes Document_Open. However, precisely what happens and what you see depend on at least the following:

它失败的原因是 Word 在执行 Document_Open 之前尝试连接到现有数据源。但是，究竟发生了什么以及您看到了什么，至少取决于以下几点：

1. whether the data source information stored in the document is still valid (i.e., is the original .xlsm still there, and e.g. is the correct Sheet name still in it)
2. whether you are opening the Word document manually or via OLE Automation
3. whether the SQLSecurityCheck registry setting described hereis in its default state (i.e. absent or set to 1), or set to 0
4. in the case of OLE Automation, whether the Word Applicaiotn object's DisplayAlerts property is set to wdAlertsAll or wdAlertsNone

1. 文档中存储的数据源信息是否仍然有效（即原始 .xlsm 是否仍然存在，例如正确的 Sheet 名称是否仍在其中）
2. 无论您是手动打开 Word 文档还是通过 OLE 自动化
3. 此处描述的 SQLSecurityCheck 注册表设置是处于默认状态（即不存在或设置为 1），还是设置为 0
4. 在 OLE 自动化的情况下，Word Appliciotn 对象的 DisplayAlerts 属性是否设置为 wdAlertsAll 或 wdAlertsNone

Roughly speaking...

大致说来...

If the data source Word tries to find still exists, in all cases except one, Word will make the connection as long as the user responds Yes to any security prompt they see. The exception is that if SQLSecurityCheck is absent or set to 1 (i.e. is the default), the document is opened via OLE Automation, and DisplayAlerts is set to wdAlertsNone, no dialog is displayed and the data source is not opened.

如果 Word 尝试查找的数据源仍然存在，则在所有情况下（除了一种情况），只要用户对他们看到的任何安全提示做出“是”响应，Word 就会建立连接。例外情况是，如果 SQLSecurityCheck 不存在或设置为 1（即默认值），文档将通过 OLE 自动化打开，并且 DisplayAlerts 设置为 wdAlertsNone，则不显示对话框并且不打开数据源。

If the data source Word tries to find does not exist (e.g. has been moved), in all cases except one the user will see an error dialog. If their response to that dialog is to identify a valid data source, the document will have a new data source. Again, the exception is when SQLSecurityCheck is the defaul value, the document is opened via OLE, and DisplayAlerts is set to wdAlertsNone. In that case, no dialog is displayed and the data source is not opened.

如果 Word 尝试查找的数据源不存在（例如已被移动），则在所有情况下，用户将看到错误对话框。如果他们对该对话框的响应是识别有效的数据源，则该文档将具有新的数据源。同样，当 SQLSecurityCheck 为默认值时，文档是通过 OLE 打开的，并且 DisplayAlerts 设置为 wdAlertsNone 时，也是例外。在这种情况下，不会显示任何对话框，也不会打开数据源。

A problem for developers is that if the SQLSecurityCheck value has been changed to 0 (typically to spare users having to answer the security check question all the time) they cannot avoid a user dialog popping up when the data source does not exist.

开发人员面临的一个问题是，如果 SQLSecurityCheck 值已更改为 0（通常是为了让用户不必一直回答安全检查问题），他们将无法避免在数据源不存在时弹出用户对话框。

However, as long as the user is able to see and respond to any dialog boxes that Word displays when connecting to the data source, the user will either end up (a) with an open document with a data source attached, or (b) an open document with no data source attached (or arguably, some other mess, e.g. the user tries to end the Word process or some such). If either of those two things happen, the Document_Open code should then be run and Word should end up connecting to the data source you want. (Although it is possible that in some cases, trying to connect to an existing data source such as a text file when Word is already connected to it using a different method may result in an error.)

但是，只要用户能够看到并响应 Word 在连接到数据源时显示的任何对话框，用户就会 (a) 得到一个带有附加数据源的打开文档，或者 (b)一个没有附加数据源的打开文档（或者可以说是其他一些乱七八糟的东西，例如用户试图结束 Word 进程等）。如果这两种情况中的任何一种发生，则应该运行 Document_Open 代码并且 Word 应该最终连接到您想要的数据源。（尽管在某些情况下，当 Word 已使用其他方法连接到文本文件等现有数据源时，尝试连接它可能会导致错误。）

Incidentally,

顺便，

• for an OLE DB connection to an Excel workbook, you should be able to omit all the parameters in your OpenDataSOurce call except Name and SQLStatement.
• AFAICS at the moment your code will always report the name of the existing data source if Word has not already removed it by the time Document_Open executes. You'd need to move the assignment to strDataSource below the OpenDataSource call to show the new name. But perhaps I've missed the point there!

• 对于到 Excel 工作簿的 OLE DB 连接，您应该能够省略 OpenDataSource 调用中除 Name 和 SQLStatement 之外的所有参数。
• 如果 Word 在 Document_Open 执行时尚未删除现有数据源的名称，则 AFAICS 目前您的代码将始终报告现有数据源的名称。您需要将分配移动到 OpenDataSource 调用下方的 strDataSource 以显示新名称。但也许我错过了那里的重点！

回答by Barbara

I had the same problem. I solved it with: 1. the MainDocumentType = wdNotAMergeDocument (must be set to normal document, otherwise word is always questioning on open). 2. after starting a vba function read the current document-path and run the attached function:

我有同样的问题。我解决了它： 1. MainDocumentType = wdNotAMergeDocument （必须设置为普通文档，否则 word 总是在打开时出现问题）。2. 启动 vba 函数后，读取当前文档路径并运行附加函数：

enter code here

 Function Start_MMerge(xdoc As Document, SBD_Name As String) As Integer
 On Error GoTo Start_MMergeError
 Dim vFile As String
 vFile = Dir(SBD_Name) 'prüft, ob es die Datei SBD_Name überhaupt gibt
 If Len(vFile) <> 0 Then

    xdoc.MailMerge.MainDocumentType = wdFormLetters 

    xdoc.MailMerge.OpenDataSource Name:= _
    SBD_Name, _
    ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
    Format:=wdOpenFormatAuto, Connection:= _
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & SBD_Name _
    & ";Mode=Read;Extended    Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";
    Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Da" _
    , SQLStatement:="SELECT * FROM `Adressen$` WHERE [E-Mail senden]='nein'", SQLStatement1:="", 
    SubType:=wdMergeSubTypeAccess
    xdoc.MailMerge.MainDocumentType = wdFormLetters

    With xdoc.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With

    Start_MMerge = -1
 Else
    Start_MMerge = 0
 End If

 Start_MMergeExit:
     xdoc.MailMerge.MainDocumentType = wdNotAMergeDocument

     Exit Function
 Start_MMergeError:
     Start_MMerge = 0
     Resume Start_MMergeExit

 End Function

enter code here