"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.
|