Mailmerge by macro

[J.Reynolds]

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.

 

[Peter Jamieson]

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]

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

 

[J.Reynolds]

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

 

[Peter Jamieson]

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]

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.