Mail-merge dynamic database source

[RizzKid]

I have created a mail-merge that gets its input from Excel via OLEDB. Each
month it needs to get the input from a spreadsheet in a folder specific to
that month. Moreover, although I create and test the merge on my pc, I
distribute it to others who have a different naming convention for their
folders. Once I save my merge document, the source folder is saved with it.
[Although I have examined the merge document carefully, and can't find any
clear text reference to the database. Does anyone know how that info is
stored?]

I know that if the database doesn't exist when the merge doc is opened,
there are prompts to find the new source. I know that even if the database
exists (but may be an old source), I can choose "Select Recipients" and
select a new source.

I have seen an earlier posting which mentions property
ActiveDocument.MailMerge.DataSource.Name, and I will experiment writing a
macro to see if that can be changed dynamically. In the meantime:

- Is there any way to reliably force the merge to prompt for the database
source each time the merge doc is opened?

- Is there any way to make the stored source path relative, e.g.,
\..\DatabaseInput?

 

[Peter Jamieson]

- Is there any way to reliably force the merge to prompt for the database

source each time the merge doc is opened?

The simplest method is probably to put an AutoOpen macro in your
document, or in the attached template, if you always want this action
for documents attached to this template. It could do:

Sub AutoOpen()
Dim dlg As Word.Dialog
Set dlg = Dialogs(wdDialogMailMergeOpenDataSource)
dlg.Show
End Sub

However, if you already have a data source attached
a. Word will typically issue its SQL warning dialog box and/or
b. if it can't find the data source, you get to change it - sort of

There is no (simple) way to prevent Word from looking for that data
source - it does it even before it executes AutoOpen. So whatever you
do, it's advisable to try to save the document with no data source
attached. Typically you do that by changing the document type back to
"Normal Word Document". Although all the fields are retained, Word will
lose any information about the data source, including filters and any
sort sequence you specified, and it will forget what type of merge
(Letter, Directory etc.). If necessary, in Word 2007 you may be able to
get around that by closing the data source using the following VBA

Sub CloseDataSource()
On Error Resume Next
ActiveDocument.MailMerge.DataSource.Close
Err.Clear
On Error GoTo 0
End Sub

To answer your questions...

[Although I have examined the merge document carefully, and can't find any
clear text reference to the database. Does anyone know how that info is
stored?]

The connection information is stored internally - you can't get at it
directly via the user interface. In the case of an OLE DB connection,
you typically provide the pathname of the workbook, and a worksheet or
range name. Word actually stores 3 pieces of information:
a. ActiveDocument.MailMerge.DataSource.Name contains the pathname of
the workbook
b. ActiveDocument.MailMerge.DataSource.ConnectString contains an OLE
DB connection string which also contains the pathname of the workbook
c. ActiveDocument.MailMerge.DataSource.QueryString contains a Jet/ACE
SQL query along the lines of

SELECT * FROM `mysheetorrange`

If you specified filter conditions and/or a sequence in Edit Recipients
the SQL will also contain a WHERE clause and/or an ORDER BY clause.

There is a fourth property called .TableName but it always appears to be
set to the same value as .QueryString. I don't know what it is for.

You cannot change either

ActiveDocument.MailMerge.DataSource.Name
or
ActiveDocument.MailMerge.DataSource.ConnectString

directly because they are read-only properties (as you have probably
discovered by now!)

You can modify

ActiveDocument.MailMerge.DataSource.QueryString

directly (e.g. to point to a diferent sheet in the same workbook, but
since in the case of Excel the query string does not specify the work
book pathname, AFAIK you cannot use that to change the workbook. The
only way to do that is either to get the user to select a new data
source, or to use

ActiveDocument.MailMerge.OpenDataSource

to do it.

Peter Jamieson

http://tips.pjmsn.me.uk

I have created a mail-merge that gets its input from Excel via OLEDB. Each
month it needs to get the input from a spreadsheet in a folder specific to
that month. Moreover, although I create and test the merge on my pc, I
distribute it to others who have a different naming convention for their
folders. Once I save my merge document, the source folder is saved with it.
[Although I have examined the merge document carefully, and can't find any
clear text reference to the database. Does anyone know how that info is
stored?]

I know that if the database doesn't exist when the merge doc is opened,
there are prompts to find the new source. I know that even if the database
exists (but may be an old source), I can choose "Select Recipients" and
select a new source.

I have seen an earlier posting which mentions property
ActiveDocument.MailMerge.DataSource.Name, and I will experiment writing a
macro to see if that can be changed dynamically. In the meantime:

- Is there any way to reliably force the merge to prompt for the database
source each time the merge doc is opened?

- Is there any way to make the stored source path relative, e.g.,
\..\DatabaseInput?