Microsoft Word邮件合并数据源自动位置 [英] Microsoft Word Mail Merge Data Source auto location

问题描述

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

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

目前excel文件的位置（数据源）

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.

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

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.

然后，在创建文件时，将出现一个消息框，其中应显示新的mailmerge数据源。

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.

代码：

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

推荐答案

它失败，因为Word试图在执行Document_Open之前连接到现有数据源。但是，确切发生的情况和您看到的内容至少取决于以下内容：

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:

1. 文档中存储的数据源信息是否仍然是
   有效（即原始的.xlsm是否仍然存在，例如
   正确的工作表名称是否仍在其中）


2. 是否手动打开Word文档或通过OLE
   Automation


3. 是否在此处描述了SQLSecurityCheck注册表设置是
   的默认状态（即不存在或设置为1），或设置为0


4. （对于OLE Automation），是否为Word Applicaiotn对象的
   DisplayAlerts属性设置为wdAlertsAll或wdAlertsNone

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 here is 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

粗略地讲...

如果Word试图查找的数据源仍然存在，则在任何一种情况下，Word都会建立连接，只要用户对看到的任何安全提示做出是答复即可。唯一的例外是，如果不存在SQLSecurityCheck或将其设置为1（即默认值），则通过OLE Automation打开文档，并将DisplayAlerts设置为wdAlertsNone，则不会显示任何对话框，也不会打开数据源。

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试图查找的数据源不存在（例如已被移动），则在所有情况下，除一种情况外，用户都将看到一个错误对话框。如果他们对该对话框的响应是要标识一个有效的数据源，则文档将具有一个新的数据源。同样，例外情况是当SQLSecurityCheck为默认值，通过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.

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

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.

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

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.)

顺便说一句，

• 对于与Excel工作簿的OLE DB连接，您应该可以省略
  OpenDataSOurce调用中的所有参数（名称和
  SQLStatement除外）。


• AFAICS现在，如果Word包含以下内容，则您的代码将始终报告
  现有数据源的名称在
  Document_Open执行时尚未将其删除。您需要将分配移动到OpenDataSource调用下方的
  strDataSource，以显示新名称。但是
  也许我错过了重点！

这篇关于Microsoft Word邮件合并数据源自动位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！