Single record merging

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter Ambrose - 12 Dec 2007 06:17 GMT

I have a SQL database - I want to merge just a single record from it.

How do I select a particular from within Word, simply, ideally driven not
from within the word document? i.e. I need to pass word a record-id on
launching it, run a macro which then mailmerges just that record?

Thanks

Peter

Reply to this Message

Peter Jamieson - 12 Dec 2007 09:38 GMT

Rather than simply running Word from the command line with a parameter, can
you automate it?

In essence Word lets you specify the records you want to merge in three
different ways:
a. For some types of merge you can use { SKIPIF } fields in the body of the
document. To do that you would have to alter the document itself before you
merged, and Word itself would iterate right through your data source. So
lots of problems there.
b. using an SQL query that is built when you select the data source, or
which Word modifies when you set sort and filter options in Query Options,
or which you provide as a parameter to Word's OpenDataSource method, or
which you provide by setting the .QueryString property of the
MailMerge.Datasource object
c. by setting MailMerge.Datasource.Included to false for every record e.g.
using .SetAllIncludedFlags Included:=False, then navigating to the record
you want, setting its Included flag to True

(Another way of course would be if you could set up a table that had one
record that specified the key of the record you needed, and use a query/view
to get the record you wanted. Then you wouldn't need to change anything in
Word. But there can be problems with that).

In essence, if you know what query you need to use (complete with the WHERE
clause that specifies the single record) you probably need to use (b), which
is arguably the simplest option. If you do not know precisely what query has
been specified then you could either
d. inspect .Querystring and try to modify its WHERE clause or
e. you would need at least to know the name(s) of the data sources unique
key fields, in which case you could probably use (c).

Then you specify the destination (printer, new document, whatever) and use
the MailMerge.Execute method to do the merge and so on.

Sorry I can't give you a chunk of code that just does it...

When automating Word MailMerge, you will probably also have to take account
of the following KB article:

http://support.microsoft.com/kb/825765/en-us

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

>I have a SQL database - I want to merge just a single record from it.
>
[quoted text clipped - 5 lines]
>
> Peter

Reply to this Message

Peter Ambrose - 12 Dec 2007 21:09 GMT

Hi Peter

Thank you for that very full answer! (Sadly my technical skills may be a
little lacking, although I can pick things up quickly!)

It sounds like you recommend (b) modifying the SQL query on the data source,
which sounds useful. I do know what query I need, although obviously I will
be passing it a variable. Problem is, how would I pass trhat variable to
Word to tell it which record to pull back.

What about if I use ASK in mailmerge, and take that input and pass it to the
SQL query?

Peter

> Rather than simply running Word from the command line with a parameter, can
> you automate it?
[quoted text clipped - 46 lines]
> >
> > Peter

Reply to this Message

Peter Jamieson - 13 Dec 2007 10:28 GMT

> I do know what query I need, although obviously I will
> be passing it a variable. Problem is, how would I pass trhat variable to
> Word to tell it which record to pull back.

The problem is that the standard Command method in VBA in Office cannot get
the whole command line passed to Word. You may be able to get the whole
command line using the following code based on a posting by a Word MVP
called Will Rickards, I think:

---------------------------------------------------------------------------------
Public Declare Function GetCommandLine Lib "kernel32" Alias
"GetCommandLineA" () As Long
Public Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal
lpString1 As String, ByVal lpString2 As Long) As Long
Public Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal
lpString As Long) As Long

Public Function LPTSTRtoString(ByVal lngPtr As Long) As String
Dim strReturn As String
Dim lngStrLen As Long

'get the length of the string (not including the terminating null character)
lngStrLen = lstrlen(lngPtr)

'initialize our string so it has enough characters including the null
character
strReturn = String$(lngStrLen + 1, 0)

'copy the string we have a pointer to into our new string
lstrcpy strReturn, lngPtr

'now strip off the null character at the end
strReturn = Left$(strReturn, lngStrLen)

'return the string
LPTSTRtoString = strReturn

End Function

Public Sub AutoOpen()
Dim strCommandLine As String
Dim strDirectory As String

'get the commandline
strCommandLine = LPTSTRtoString(GetCommandLine())

' but then you will have to process the command line to get you parameter.
' and do what you need with it

End Sub
---------------------------------------------------------------------------------

Otherwise, if you are /automating/ from outside Word, for example using
VBScript, then what you do is
a. create a new Word object
b. open the MailMerge Main document (which will then try to connect to the
existing data source)
c. set the value of QueryString
d. execute the merge (and unfortunately, it may not be easy to see when it
has finished if you are merging to printer or email)
e. close everything
f. quit word

It's a bit difficult to show you how without knowing more about your
starting point, but if you search this group for messages containing the
word "CreateObject", you'll find plenty of examples of how automation is
usually done (but probably not many that script from /outside/ Word)

> What about if I use ASK in mailmerge, and take that input and pass it to
> the
> SQL query?

At this point it's too late, and there's no way to get the ASK to modify the
query. However, if you are happy to do something along those lines you could
have a Word VBA AutoOpen macro that
a. used the inputbox function to display the prompt and gather a record
number (interaction required)
b. set .Querystring
c. did the merge etc.

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> Hi Peter
>
[quoted text clipped - 79 lines]
>> >
>> > Peter

Reply to this Message