Can you please help with appropriate VBA code to filter records in a mailmerge.

The basics are as follows:

1. You either have to call the yourdocumentobject.MailMerge.OpenDataSource
method to (re-)open the data source, passing the appropriate SQL, or set the
value of yourdocumentobject.MailMerge.DataSource.QueryStr ing to the
appropriate SQL.

2. In Word 2002/2003 you can also include/exclude individual records, but in
my experience it is not reliable and I would avoid it.

3. What actually works depends largely on the data source and partly on the
version of Word. Modifying the QueryString may sound as if it is the easiest
approach, but it does not always work and you will need to determine in your
case whether it does or does not (for example, in some cases, changing the
Query string may not make sense unless you also change the ConnectString and
I believe that requires an OpenDataSource call.

4. Before calling OpenDataSource it's as well to disconnect the existing
source to avoid locking problems in ssome cases. You can probably do it
using

yourdocumentobject.MailMerge.DataSource.Close

but I don't know how reliable that is as I have generally used

yourdocumentobject.MailMerge.MainDocumentType = wdNotAMergeDocument

which would typically require you to save your Main Document type and
Destination and restore them later.

5. If you call OpenDataSource, you generally need to pass at least 3
parameters:
Name
Connection
SQLStatement

6. SQLStatement (and the other 2 parameters) are limited to 255 characters
and if you have a longer SQL string you will need to use SQLStatement1 as
well, which should take you up to around 511 characters. However, some data
sources only let you use around 255. e.g. if you have

SELECT myfield1, myfield2,....,myfieldn FROM mytable

then you might set

SQLStatement:="SELECT myfield1, myfield2,....,myfieldn"
SQLStatement1:=" FROM mytable"

i.e. make sure you insert the white space you need.

7. With Word 2002/2003 you may also need to pass the Subtype parameter,
which is not very well documented.

8. Most of the other parameters in the OpenDataSource call do nothing and
are not needed.

9. Beyond that, it gets complicated, because
a. each type of data source may use a different dialect of SQL
b. some types of data source may be opened in several different ways
depending on what is in the Name, Connection and Subtype parameters
c. some types of connection typically require a .odc or .udl file as well.

10. if you are only using one type of data source, your best bet is probably
to use the VBA Macro recorder to record opening a data source, and examine
the connection string and SQL generated, then build on that. But bewa
Word 2002 does not always record that stuff, and Word often truncates the
Connection string so that when you try to re-execute the recorded macro, it
fails. In that case, you generally have to shorten and correct the
connection string manually.

11. If you go to Google Groups and seach this group for, say,

Jamieson OpenDataSource SQLStatement

you will probably find plenty of examples. If you have a specific
requirement, let us know what it is and I will do my best.

12. If you happen to read German and happen to be able to get hold of a copy
easily, most of what I know on this subject is recorded in chapter 6 of
"Word-Programmierung - Das Handbuch, by CIndy Meister, Thomas Gahler,
Christian Fressdorf, Microsoft Press Deutschland, ISBN 3-86063-989-7.

Peter Jamieson


"Bev" wrote in message
...
Can you please help with appropriate VBA code to filter records in a
mailmerge.

Thanks Peter for your help. I've done my best to interpret your
instructions, and I've spent endless hours reading as much as I could on the
web site you directed me to but I'm still stuck. I'm having trouble with the
SQL statement area. Ive tried recording a macro to get the appropriate
syntax in the SQL statement but the code never displays the SQL options I've
selected, it just shows as:
SQLStatement:="", SQLStatement1:=""
The filtering options I selected while recording that macro did produce the
result I wanted, but I wanted to produce that result in VBA automatically.
I'm using Word2003 and accessing a Word table, which I want to filter using
a "Tick" column which selects only those records with a "1" in it.
Any further help you could give me I'd very much appreciate.
Thanks Bev


"Peter Jamieson" wrote:

The basics are as follows:

1. You either have to call the yourdocumentobject.MailMerge.OpenDataSource
method to (re-)open the data source, passing the appropriate SQL, or set the
value of yourdocumentobject.MailMerge.DataSource.QueryStr ing to the
appropriate SQL.

2. In Word 2002/2003 you can also include/exclude individual records, but in
my experience it is not reliable and I would avoid it.

3. What actually works depends largely on the data source and partly on the
version of Word. Modifying the QueryString may sound as if it is the easiest
approach, but it does not always work and you will need to determine in your
case whether it does or does not (for example, in some cases, changing the
Query string may not make sense unless you also change the ConnectString and
I believe that requires an OpenDataSource call.

4. Before calling OpenDataSource it's as well to disconnect the existing
source to avoid locking problems in ssome cases. You can probably do it
using

yourdocumentobject.MailMerge.DataSource.Close

but I don't know how reliable that is as I have generally used

yourdocumentobject.MailMerge.MainDocumentType = wdNotAMergeDocument

which would typically require you to save your Main Document type and
Destination and restore them later.

5. If you call OpenDataSource, you generally need to pass at least 3
parameters:
Name
Connection
SQLStatement

6. SQLStatement (and the other 2 parameters) are limited to 255 characters
and if you have a longer SQL string you will need to use SQLStatement1 as
well, which should take you up to around 511 characters. However, some data
sources only let you use around 255. e.g. if you have

SELECT myfield1, myfield2,....,myfieldn FROM mytable

then you might set

SQLStatement:="SELECT myfield1, myfield2,....,myfieldn"
SQLStatement1:=" FROM mytable"

i.e. make sure you insert the white space you need.

7. With Word 2002/2003 you may also need to pass the Subtype parameter,
which is not very well documented.

8. Most of the other parameters in the OpenDataSource call do nothing and
are not needed.

9. Beyond that, it gets complicated, because
a. each type of data source may use a different dialect of SQL
b. some types of data source may be opened in several different ways
depending on what is in the Name, Connection and Subtype parameters
c. some types of connection typically require a .odc or .udl file as well.

10. if you are only using one type of data source, your best bet is probably
to use the VBA Macro recorder to record opening a data source, and examine
the connection string and SQL generated, then build on that. But bewa
Word 2002 does not always record that stuff, and Word often truncates the
Connection string so that when you try to re-execute the recorded macro, it
fails. In that case, you generally have to shorten and correct the
connection string manually.

11. If you go to Google Groups and seach this group for, say,

Jamieson OpenDataSource SQLStatement

you will probably find plenty of examples. If you have a specific
requirement, let us know what it is and I will do my best.

12. If you happen to read German and happen to be able to get hold of a copy
easily, most of what I know on this subject is recorded in chapter 6 of
"Word-Programmierung - Das Handbuch, by CIndy Meister, Thomas Gahler,
Christian Fressdorf, Microsoft Press Deutschland, ISBN 3-86063-989-7.

Peter Jamieson


"Bev" wrote in message
...
Can you please help with appropriate VBA code to filter records in a
mailmerge.

My previous message was probably overkill for your needs - also I'd
forgotten that changing stuff in Mail Merge Recipients generally does not
record in the macro recorder in Word 2002/3. There are a few ways you can
discover the necessary SQL:
a. apply a sample filter manually in Word, then print the value of

ActiveDocument.MailMerge.DataSource.QueryString

(
you can do that by typing the following in the VBE Immediate window

print ActiveDocument.MAilMerge.DataSource.QueryString

or by executing a sub, e.g.

Sub showQS()

Debug.Print ActiveDocument.MAilMerge.DataSource.QueryString
'or
' Msgbox ActiveDocument.MAilMerge.DataSource.QueryString

End Sub
)

b. Go into Tools|Customize|Commands, select category All Commands, locate
the MailMergeHelper command in the list of commands and drag it to a toolbar
(e.g. the Mail Merge toolbar). If you switch on the macro recorder then use
the Mail Merge Helper to set the Query Options, you should see all the
relevant code.
c. start with a blank document. enable the database toolbar. Use the Insert
database icon to select your Word data source, specify query options, then
insert the data as a field. Use Alt-F9 to display the resulting { DATABASE }
field and you should see what the SQL looks like.

(I'm sure you only need /one/ of those methods but (b) and/or (c) can be
useful if you are experimenting!).

As for your "tick" comparison, you will probably need either

SELECT * FROM C:\mydoc.doc WHERE ((tick = 1))"

or

SELECT * FROM C:\mydoc.doc WHERE ((tick = '1'))"

depending on what else is in the "tick" column. I believe, but am not
certain, that if the first tick value is numeric, Word will consider the
entire column to be numeric and will either insist on or favour WHERE ((tick
= 1)). If the first value of tick is an alpha value such as Y then Word will
probably consider the entire column to be alpha and will either favour or
insist on WHERE ((tick = '1')).

Peter Jamieson

"Bev" wrote in message
...
Thanks Peter for your help. I've done my best to interpret your
instructions, and I've spent endless hours reading as much as I could on
the
web site you directed me to but I'm still stuck. I'm having trouble with
the
SQL statement area. Ive tried recording a macro to get the appropriate
syntax in the SQL statement but the code never displays the SQL options
I've
selected, it just shows as:
SQLStatement:="", SQLStatement1:=""
The filtering options I selected while recording that macro did produce
the
result I wanted, but I wanted to produce that result in VBA automatically.
I'm using Word2003 and accessing a Word table, which I want to filter
using
a "Tick" column which selects only those records with a "1" in it.
Any further help you could give me I'd very much appreciate.
Thanks Bev


"Peter Jamieson" wrote:

The basics are as follows:

1. You either have to call the
yourdocumentobject.MailMerge.OpenDataSource
method to (re-)open the data source, passing the appropriate SQL, or set
the
value of yourdocumentobject.MailMerge.DataSource.QueryStr ing to the
appropriate SQL.

2. In Word 2002/2003 you can also include/exclude individual records, but
in
my experience it is not reliable and I would avoid it.

3. What actually works depends largely on the data source and partly on
the
version of Word. Modifying the QueryString may sound as if it is the
easiest
approach, but it does not always work and you will need to determine in
your
case whether it does or does not (for example, in some cases, changing
the
Query string may not make sense unless you also change the ConnectString
and
I believe that requires an OpenDataSource call.

4. Before calling OpenDataSource it's as well to disconnect the existing
source to avoid locking problems in ssome cases. You can probably do it
using

yourdocumentobject.MailMerge.DataSource.Close

but I don't know how reliable that is as I have generally used

yourdocumentobject.MailMerge.MainDocumentType = wdNotAMergeDocument

which would typically require you to save your Main Document type and
Destination and restore them later.

5. If you call OpenDataSource, you generally need to pass at least 3
parameters:
Name
Connection
SQLStatement

6. SQLStatement (and the other 2 parameters) are limited to 255
characters
and if you have a longer SQL string you will need to use SQLStatement1 as
well, which should take you up to around 511 characters. However, some
data
sources only let you use around 255. e.g. if you have

SELECT myfield1, myfield2,....,myfieldn FROM mytable

then you might set

SQLStatement:="SELECT myfield1, myfield2,....,myfieldn"
SQLStatement1:=" FROM mytable"

i.e. make sure you insert the white space you need.

7. With Word 2002/2003 you may also need to pass the Subtype parameter,
which is not very well documented.

8. Most of the other parameters in the OpenDataSource call do nothing and
are not needed.

9. Beyond that, it gets complicated, because
a. each type of data source may use a different dialect of SQL
b. some types of data source may be opened in several different ways
depending on what is in the Name, Connection and Subtype parameters
c. some types of connection typically require a .odc or .udl file as
well.

10. if you are only using one type of data source, your best bet is
probably
to use the VBA Macro recorder to record opening a data source, and
examine
the connection string and SQL generated, then build on that. But bewa
Word 2002 does not always record that stuff, and Word often truncates the
Connection string so that when you try to re-execute the recorded macro,
it
fails. In that case, you generally have to shorten and correct the
connection string manually.

11. If you go to Google Groups and seach this group for, say,

Jamieson OpenDataSource SQLStatement

you will probably find plenty of examples. If you have a specific
requirement, let us know what it is and I will do my best.

12. If you happen to read German and happen to be able to get hold of a
copy
easily, most of what I know on this subject is recorded in chapter 6 of
"Word-Programmierung - Das Handbuch, by CIndy Meister, Thomas Gahler,
Christian Fressdorf, Microsoft Press Deutschland, ISBN 3-86063-989-7.

Peter Jamieson


"Bev" wrote in message
...
Can you please help with appropriate VBA code to filter records in a
mailmerge.

Peter thank you very much. Your help was right on the button, even giving me
more for later projects that I can see will be extremely helpful.

What can I say, you guys are great. Thanks heaps.
Bev

"Peter Jamieson" wrote:

My previous message was probably overkill for your needs - also I'd
forgotten that changing stuff in Mail Merge Recipients generally does not
record in the macro recorder in Word 2002/3. There are a few ways you can
discover the necessary SQL:
a. apply a sample filter manually in Word, then print the value of

ActiveDocument.MailMerge.DataSource.QueryString

(
you can do that by typing the following in the VBE Immediate window

print ActiveDocument.MAilMerge.DataSource.QueryString

or by executing a sub, e.g.

Sub showQS()

Debug.Print ActiveDocument.MAilMerge.DataSource.QueryString
'or
' Msgbox ActiveDocument.MAilMerge.DataSource.QueryString

End Sub
)

b. Go into Tools|Customize|Commands, select category All Commands, locate
the MailMergeHelper command in the list of commands and drag it to a toolbar
(e.g. the Mail Merge toolbar). If you switch on the macro recorder then use
the Mail Merge Helper to set the Query Options, you should see all the
relevant code.
c. start with a blank document. enable the database toolbar. Use the Insert
database icon to select your Word data source, specify query options, then
insert the data as a field. Use Alt-F9 to display the resulting { DATABASE }
field and you should see what the SQL looks like.

(I'm sure you only need /one/ of those methods but (b) and/or (c) can be
useful if you are experimenting!).

As for your "tick" comparison, you will probably need either

SELECT * FROM C:\mydoc.doc WHERE ((tick = 1))"

or

SELECT * FROM C:\mydoc.doc WHERE ((tick = '1'))"

depending on what else is in the "tick" column. I believe, but am not
certain, that if the first tick value is numeric, Word will consider the
entire column to be numeric and will either insist on or favour WHERE ((tick
= 1)). If the first value of tick is an alpha value such as Y then Word will
probably consider the entire column to be alpha and will either favour or
insist on WHERE ((tick = '1')).

Peter Jamieson

"Bev" wrote in message
...
Thanks Peter for your help. I've done my best to interpret your
instructions, and I've spent endless hours reading as much as I could on
the
web site you directed me to but I'm still stuck. I'm having trouble with
the
SQL statement area. Ive tried recording a macro to get the appropriate
syntax in the SQL statement but the code never displays the SQL options
I've
selected, it just shows as:
SQLStatement:="", SQLStatement1:=""
The filtering options I selected while recording that macro did produce
the
result I wanted, but I wanted to produce that result in VBA automatically.
I'm using Word2003 and accessing a Word table, which I want to filter
using
a "Tick" column which selects only those records with a "1" in it.
Any further help you could give me I'd very much appreciate.
Thanks Bev


"Peter Jamieson" wrote:

The basics are as follows:

1. You either have to call the
yourdocumentobject.MailMerge.OpenDataSource
method to (re-)open the data source, passing the appropriate SQL, or set
the
value of yourdocumentobject.MailMerge.DataSource.QueryStr ing to the
appropriate SQL.

2. In Word 2002/2003 you can also include/exclude individual records, but
in
my experience it is not reliable and I would avoid it.

3. What actually works depends largely on the data source and partly on
the
version of Word. Modifying the QueryString may sound as if it is the
easiest
approach, but it does not always work and you will need to determine in
your
case whether it does or does not (for example, in some cases, changing
the
Query string may not make sense unless you also change the ConnectString
and
I believe that requires an OpenDataSource call.

4. Before calling OpenDataSource it's as well to disconnect the existing
source to avoid locking problems in ssome cases. You can probably do it
using

yourdocumentobject.MailMerge.DataSource.Close

but I don't know how reliable that is as I have generally used

yourdocumentobject.MailMerge.MainDocumentType = wdNotAMergeDocument

which would typically require you to save your Main Document type and
Destination and restore them later.

5. If you call OpenDataSource, you generally need to pass at least 3
parameters:
Name
Connection
SQLStatement

6. SQLStatement (and the other 2 parameters) are limited to 255
characters
and if you have a longer SQL string you will need to use SQLStatement1 as
well, which should take you up to around 511 characters. However, some
data
sources only let you use around 255. e.g. if you have

SELECT myfield1, myfield2,....,myfieldn FROM mytable

then you might set

SQLStatement:="SELECT myfield1, myfield2,....,myfieldn"
SQLStatement1:=" FROM mytable"

i.e. make sure you insert the white space you need.

7. With Word 2002/2003 you may also need to pass the Subtype parameter,
which is not very well documented.

8. Most of the other parameters in the OpenDataSource call do nothing and
are not needed.

9. Beyond that, it gets complicated, because
a. each type of data source may use a different dialect of SQL
b. some types of data source may be opened in several different ways
depending on what is in the Name, Connection and Subtype parameters
c. some types of connection typically require a .odc or .udl file as
well.

10. if you are only using one type of data source, your best bet is
probably
to use the VBA Macro recorder to record opening a data source, and
examine
the connection string and SQL generated, then build on that. But bewa
Word 2002 does not always record that stuff, and Word often truncates the
Connection string so that when you try to re-execute the recorded macro,
it
fails. In that case, you generally have to shorten and correct the
connection string manually.

11. If you go to Google Groups and seach this group for, say,

Jamieson OpenDataSource SQLStatement

you will probably find plenty of examples. If you have a specific
requirement, let us know what it is and I will do my best.

12. If you happen to read German and happen to be able to get hold of a
copy
easily, most of what I know on this subject is recorded in chapter 6 of
"Word-Programmierung - Das Handbuch, by CIndy Meister, Thomas Gahler,
Christian Fressdorf, Microsoft Press Deutschland, ISBN 3-86063-989-7.

Peter Jamieson


"Bev" wrote in message
...
Can you please help with appropriate VBA code to filter records in a
mailmerge.