I am using word and excel 2002. I filter and save data in an excel worksheet.
When I open it from the mailmerge toolbar and click on mailmerge recipients
I need to filter again to loose the blank rows, I also sort 2 other columns.
This gives me the labels required. But I want to run it on a macro. The
macro wont filter or sort, it gives me my labels plus three hundred pages of
empty labels. Looking at the macro to edit it, there is a gap where the
filter and sort should be. I dont know how to write (fill in) the VBA to
produce the required result.

Word does filtering in two ways:
a. when you pick individual records in the mail merge Recipients dialog
box, Word just marks individual records as included or amitted in its
internal list
b. when you apply sorts and filters, Word generates a new SQL command and
re-queries the data source.

You almost certainly need to do (b), and what that means is that you need
c. to work out what SQL you need
d. to close and re-open the data source using OpenDataSource and providing
that SQL or
e. to set your document object .Mailmerge.DataSource.QueryString to that
SQL

(e) doesn't always seem to work so you may be forced to do (d).

You may find it is difficult to discover the typical SQL for an Excel data
source because there is an error in Word 2002 that (sometimes, if not
always) crashes Word when you try to display the value of the ConnectString
and Query string. If so, make the connection and the filter and sort
manually, save the document as a Web Page, close it, then open the main .htm
file in notepad. The merge settings should be visible near the top of the
file. That said, the OpenDataSource for a typical Excel data source can
leave a lot of the parameters blank or leave them out altogether, e.g. try

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls", _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeAccess

Actually, you can probably get away with

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls"

but you will certainly need the SQLStatement parameter if you want to issue
some SQL when you connect.

An example of how to set an SQL command modified with a simple filter and
sort is

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil` ASC "
& ""

Peter Jamieson
"J.Reynolds" wrote in message
...
I am using word and excel 2002. I filter and save data in an excel
worksheet.
When I open it from the mailmerge toolbar and click on mailmerge
recipients
I need to filter again to loose the blank rows, I also sort 2 other
columns.
This gives me the labels required. But I want to run it on a macro. The
macro wont filter or sort, it gives me my labels plus three hundred pages
of
empty labels. Looking at the macro to edit it, there is a gap where the
filter and sort should be. I dont know how to write (fill in) the VBA to
produce the required result.

I copied and pasted your last suggestion into my macro changing the column
headings but it wont run.

"Peter Jamieson" wrote:

Word does filtering in two ways:
a. when you pick individual records in the mail merge Recipients dialog
box, Word just marks individual records as included or amitted in its
internal list
b. when you apply sorts and filters, Word generates a new SQL command and
re-queries the data source.

You almost certainly need to do (b), and what that means is that you need
c. to work out what SQL you need
d. to close and re-open the data source using OpenDataSource and providing
that SQL or
e. to set your document object .Mailmerge.DataSource.QueryString to that
SQL

(e) doesn't always seem to work so you may be forced to do (d).

You may find it is difficult to discover the typical SQL for an Excel data
source because there is an error in Word 2002 that (sometimes, if not
always) crashes Word when you try to display the value of the ConnectString
and Query string. If so, make the connection and the filter and sort
manually, save the document as a Web Page, close it, then open the main .htm
file in notepad. The merge settings should be visible near the top of the
file. That said, the OpenDataSource for a typical Excel data source can
leave a lot of the parameters blank or leave them out altogether, e.g. try

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls", _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeAccess

Actually, you can probably get away with

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls"

but you will certainly need the SQLStatement parameter if you want to issue
some SQL when you connect.

An example of how to set an SQL command modified with a simple filter and
sort is

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil` ASC "
& ""

Peter Jamieson
"J.Reynolds" wrote in message
...
I am using word and excel 2002. I filter and save data in an excel
worksheet.
When I open it from the mailmerge toolbar and click on mailmerge
recipients
I need to filter again to loose the blank rows, I also sort 2 other
columns.
This gives me the labels required. But I want to run it on a macro. The
macro wont filter or sort, it gives me my labels plus three hundred pages
of
empty labels. Looking at the macro to edit it, there is a gap where the
filter and sort should be. I dont know how to write (fill in) the VBA to
produce the required result.

"J.Reynolds" wrote:

I copied and pasted your last suggestion into my macro changing the column
headings but it wont run.

"Peter Jamieson" wrote:

Word does filtering in two ways:
a. when you pick individual records in the mail merge Recipients dialog
box, Word just marks individual records as included or amitted in its
internal list
b. when you apply sorts and filters, Word generates a new SQL command and
re-queries the data source.

You almost certainly need to do (b), and what that means is that you need
c. to work out what SQL you need
d. to close and re-open the data source using OpenDataSource and providing
that SQL or
e. to set your document object .Mailmerge.DataSource.QueryString to that
SQL

(e) doesn't always seem to work so you may be forced to do (d).

You may find it is difficult to discover the typical SQL for an Excel data
source because there is an error in Word 2002 that (sometimes, if not
always) crashes Word when you try to display the value of the ConnectString
and Query string. If so, make the connection and the filter and sort
manually, save the document as a Web Page, close it, then open the main .htm
file in notepad. The merge settings should be visible near the top of the
file. That said, the OpenDataSource for a typical Excel data source can
leave a lot of the parameters blank or leave them out altogether, e.g. try

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls", _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeAccess

Actually, you can probably get away with

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls"

but you will certainly need the SQLStatement parameter if you want to issue
some SQL when you connect.

An example of how to set an SQL command modified with a simple filter and
sort is

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil` ASC "
& ""

Peter Jamieson
"J.Reynolds" wrote in message
...
I am using word and excel 2002. I filter and save data in an excel
worksheet.
When I open it from the mailmerge toolbar and click on mailmerge
recipients
I need to filter again to loose the blank rows, I also sort 2 other
columns.
This gives me the labels required. But I want to run it on a macro. The
macro wont filter or sort, it gives me my labels plus three hundred pages
of
empty labels. Looking at the macro to edit it, there is a gap where the
filter and sort should be. I dont know how to write (fill in) the VBA to
produce the required result.

What exactly did you paste in? this was just a piece of sample code and the
SQL uses field names that I happen to have in a test Spreadsheet here. You
need at least to adapt the field names to your situation.

Peter Jamieson
"J.Reynolds" wrote in message
...


"J.Reynolds" wrote:

I copied and pasted your last suggestion into my macro changing the
column
headings but it wont run.

"Peter Jamieson" wrote:

Word does filtering in two ways:
a. when you pick individual records in the mail merge Recipients
dialog
box, Word just marks individual records as included or amitted in its
internal list
b. when you apply sorts and filters, Word generates a new SQL command
and
re-queries the data source.

You almost certainly need to do (b), and what that means is that you
need
c. to work out what SQL you need
d. to close and re-open the data source using OpenDataSource and
providing
that SQL or
e. to set your document object .Mailmerge.DataSource.QueryString to
that
SQL

(e) doesn't always seem to work so you may be forced to do (d).

You may find it is difficult to discover the typical SQL for an Excel
data
source because there is an error in Word 2002 that (sometimes, if not
always) crashes Word when you try to display the value of the
ConnectString
and Query string. If so, make the connection and the filter and sort
manually, save the document as a Web Page, close it, then open the main
.htm
file in notepad. The merge settings should be visible near the top of
the
file. That said, the OpenDataSource for a typical Excel data source can
leave a lot of the parameters blank or leave them out altogether, e.g.
try

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls", _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeAccess

Actually, you can probably get away with

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls"

but you will certainly need the SQLStatement parameter if you want to
issue
some SQL when you connect.

An example of how to set an SQL command modified with a simple filter
and
sort is

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil`
ASC "
& ""

Peter Jamieson
"J.Reynolds" wrote in message
...
I am using word and excel 2002. I filter and save data in an excel
worksheet.
When I open it from the mailmerge toolbar and click on mailmerge
recipients
I need to filter again to loose the blank rows, I also sort 2 other
columns.
This gives me the labels required. But I want to run it on a macro.
The
macro wont filter or sort, it gives me my labels plus three hundred
pages
of
empty labels. Looking at the macro to edit it, there is a gap where
the
filter and sort should be. I dont know how to write (fill in) the VBA
to
produce the required result.

After reading through other peoples merge difficulties I found someone who
wanted to revert to the 2000 mailmerge system. This gives a query box which
when used running the macro actually records the query. The result was
ActiveDocument.Mailmerge.DataSource.QueryString = "SELECT * FROM 'Sheet 1$'
WHERE ('Name' IS NOT NULL And 'Name' ") ORDER BY 'Product' ASC, 'Size' ASC"
&"" . I may have missed or included an extra space, but it works. Thanks
for your help. It is great to know there is someone out there who knows what
they are doing.

"Peter Jamieson" wrote:

What exactly did you paste in? this was just a piece of sample code and the
SQL uses field names that I happen to have in a test Spreadsheet here. You
need at least to adapt the field names to your situation.

Peter Jamieson
"J.Reynolds" wrote in message
...


"J.Reynolds" wrote:

I copied and pasted your last suggestion into my macro changing the
column
headings but it wont run.

"Peter Jamieson" wrote:

Word does filtering in two ways:
a. when you pick individual records in the mail merge Recipients
dialog
box, Word just marks individual records as included or amitted in its
internal list
b. when you apply sorts and filters, Word generates a new SQL command
and
re-queries the data source.

You almost certainly need to do (b), and what that means is that you
need
c. to work out what SQL you need
d. to close and re-open the data source using OpenDataSource and
providing
that SQL or
e. to set your document object .Mailmerge.DataSource.QueryString to
that
SQL

(e) doesn't always seem to work so you may be forced to do (d).

You may find it is difficult to discover the typical SQL for an Excel
data
source because there is an error in Word 2002 that (sometimes, if not
always) crashes Word when you try to display the value of the
ConnectString
and Query string. If so, make the connection and the filter and sort
manually, save the document as a Web Page, close it, then open the main
.htm
file in notepad. The merge settings should be visible near the top of
the
file. That said, the OpenDataSource for a typical Excel data source can
leave a lot of the parameters blank or leave them out altogether, e.g.
try

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls", _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeAccess

Actually, you can probably get away with

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myxls\wb.xls"

but you will certainly need the SQLStatement parameter if you want to
issue
some SQL when you connect.

An example of how to set an SQL command modified with a simple filter
and
sort is

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil`
ASC "
& ""

Peter Jamieson
"J.Reynolds" wrote in message
...
I am using word and excel 2002. I filter and save data in an excel
worksheet.
When I open it from the mailmerge toolbar and click on mailmerge
recipients
I need to filter again to loose the blank rows, I also sort 2 other
columns.
This gives me the labels required. But I want to run it on a macro.
The
macro wont filter or sort, it gives me my labels plus three hundred
pages
of
empty labels. Looking at the macro to edit it, there is a gap where
the
filter and sort should be. I dont know how to write (fill in) the VBA
to
produce the required result.