Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Mail-merge dynamic database source
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? |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Mail-merge dynamic database source
- 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 On 08/01/2010 15:25, RizzKid wrote: 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? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I maintain a database used as a source for mail functions | Microsoft Word Help | |||
How can I maintain a database used as a source for mail functions | Mailmerge | |||
Problem with mail merge using Access Database as source | Microsoft Word Help | |||
Dynamic mail merge question | Mailmerge | |||
Using as secured Access database as the data source for a mail mer | Mailmerge |