VBA Code to specify query options

[JSU_CC]

I have a text file that contains a listing of students applying for
admission and I need to generate a letter based on a number of
criteria. There are a total of 12 letters and selection of the
appropriate letter depends on several codes and a degree type.

I have created a macro several years ago to handle this in WordPerfect,
but now need to do the same in Word. I am using XP and I am a bit
frustrated using the Filter & Sort dialog box within Word. I would
prefer to handle this with VBA, but I haven't quite found what I need.

Can somebody point me in the right direction on what is the best
solution to handle this type of merge application?

Thanks in advance.

Kristin

 

[Peter Jamieson]

There are various things you can consider.

Word 2002 VBA uses the Mailmerge object and specifically the
MailMerge.DataSource object to get at Mailmerge source data. Word VBA has a
couple of facilities that correspond to the Sort/Filter options:
a. you can use the Included property to determine whether or not a specific
record in the current list in the data source is included or excluded. This
is new in Word 2002 and IMO suffers from some logical difficulties (what
happens when the data source changes?), but is the thing that corresponds to
the check boxes down the left hand side of the Select Recipients dialog box.
You probably need to use Activerecord and wdFirstDataSourceRecord,
wdNextDataSOurceRecord etc. to get at the values for all records
b. for everything else, you have to construct the SQL query yourself, and
specify it in an OpenDataSource method or by setting the Querystring. In
other words, there are no VBA objects which directly mirror the sort/filter
dialog box and which will construct this query for you. For any given type
of data source it should be possible to work out what SQL syntax is required
and put together the correct string in VBA. Doing it more generally (and
working out what SQL features are recognised etc.) is a more difficult
problem.

You can consider selecting in the data source for some types of data source.
E.g. if it is Access, you may be able to create the queries you
need and link to the appropriate query from within Word.

In Word 2002 and later you can consider using Word's Mailmerge Events to
filter the data source, either at the beginning of the merge operation or
for each record in the data source. If working with Word events is new, this
might be a bit painful, but I did find the examples in Word Help were enough
to get me started.