Word 2003 VBA Mail Merge SQL filter of Excel data

[Paul J]

I succesfully created a Word 2003 VBA macro that will produce labels from all
the records in an excel workbook (by using the macro recorder and then
editing in a line stating "Application.MailingLabel.CreateNewDocument
Name:="L7160"").

To do this the MailMerge.OpenDataSource statement presently includes the
following SQL statements:
SQLStatement:="SELECT * FROM `LOG$` ", SQLStatement1:="",

NB "LOG" is the name of the Excel worksheet in my workbook.

I need to be able to apply a filter for this mail merge so that only those
records that have a specific value (e.g. "27/02/07" in text or in Excel date
format) in the field that is headed "LOGDateOnly" are produced as labels.

After hours of internet searching and trials of possible SQL incantations I
now throw myself on the mercy of this forum.

Am I correct in searching for an SQL statement to achieve the filer? If so,
what can it be? If not, how can I achieve the Mail Merge Recipients
filtering capability?

Many thanks in advance.

Paul J

 

[Cindy M.]

Hi Paul,

Take a look at the DataSource.QueryString property.

To get a feel for the syntax Word wants to use, record a macro while going
through the Recipients dialog box. Click on the arrow next to a field and choose
the "Advanced" option, which should bring up the Query Options dialog box. Set
the criteria there and that should record this property for you.

I succesfully created a Word 2003 VBA macro that will produce labels from all
the records in an excel workbook (by using the macro recorder and then
editing in a line stating "Application.MailingLabel.CreateNewDocument
Name:="L7160"").

To do this the MailMerge.OpenDataSource statement presently includes the
following SQL statements:
SQLStatement:="SELECT * FROM `LOG$` ", SQLStatement1:="",

NB "LOG" is the name of the Excel worksheet in my workbook.

I need to be able to apply a filter for this mail merge so that only those
records that have a specific value (e.g. "27/02/07" in text or in Excel date
format) in the field that is headed "LOGDateOnly" are produced as labels.

After hours of internet searching and trials of possible SQL incantations I
now throw myself on the mercy of this forum.

Am I correct in searching for an SQL statement to achieve the filer? If so,
what can it be? If not, how can I achieve the Mail Merge Recipients
filtering capability?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Paul J]

Hi Cindy,

Thanks for your swift response.

Regretably using the "advanced" option on the Recipients Dialog box brought
about no change in the SQL statements produced by the VBA recorder despite
the filters having correctly produced the labels I want.

It has been noted elsewhere that Word 2003/VBA Macro Recorder does not
accuratley record the MailMerge sequence (hence the need for me to manually
add a "MailingLabel.CreateNewDocument" statement - see below).

Could you therefore offer me some more direct SQL guidance as to how to
identify the Excel field(s) that I wish to filter and how I can select only
the records I need.

Yours, again in anticipation,

Paul J

 

[Cindy M.]

Hi =?Utf-8?B?UGF1bCBK?=,

Regretably using the "advanced" option on the Recipients Dialog box brought
about no change in the SQL statements produced by the VBA recorder despite
the filters having correctly produced the labels I want.

You misunderstood me. It changes the DataSource.Querystring property. This is
separate from the SQLStatement parameters of the OpenDataSource method. It can
be set at any time (and not just when the data source connects).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail