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.