[[email protected]]

On May 14, 10:29 am, "Peter Jamieson"
wrote:
1. Once you have set up the data source in the Word document, Word should
not forget it (whatever it is) - in the case of an Excel document it should
remember everything it needs to know - sheet, selection criteria, and
sequence criteria.

2. However, if you need to do the OpenDataSource or modify the Sheet in your
macro, things are slightly different. Typically, if you macro record the
process of connecting to a data source, Word will record an OpenDataSource
method call. During initial connection you don't usually get an opportunity
to select anything except the sheet or a named range (unless you go through
MS Query, which is a different subject). In the recorded macro you will
probably see that WOrd records the SQLStatement parameter of the
OpenDataSource call as something like

"SELECT * FROM `Sheet1$`"

If you then use Edit Recipients to filter and sequence the data, Word may do
two things:
a. if you select/deselct individual rows, Word actually stores information
internally about the rows. This is a highly unpredictable process for a
number of reasons - what, for example, if you sort the data source in Excel.
How is Word supposed to identify which row you meant when you checked its
box? If you disconnect the data source, as far as I know, Word will lose any
information about which rows were selected anyway. So avoid that.
b. it may generate a new SQL statement from your criteria and assign it to

ActiveDocument.MailMerge.DataSource.QueryString

So, as long as you do not allow the query criteria to be changed, you should
be able to print the value of that property (e.g. in the Immediate Window in
the VBA Editor, and use it either in your own OpenDataSource call or in just
the same way WOrd does it, i.e. by setting that .QueryString property.

However, if you want people to be able to modify the query, the problem is
that you then have to generate the necessary SQL programmatically, and
although I suspect it's not that hard, there are potentially a number of
gotchas.

What I suggest is that
a. you try to ensure that the Word document is not connected to any data
source before you open it (otherwise, Word will /always/ look for that
source before you can do anything, even in VBA)
b. you make the connection in Word using OpenDataSource. Word inserts a lot
of parameters in this call, but most of them do nothing. In this case you
can probably get away with just the Name and SQLStatement parameters, e.g.

objDocument.MailMerge.OpenDataSource _
Name:="the full path name of your Excel workbook", _
SQLStatement:="the SELECT statement you got from .Querystring"

I suspect that might be rather confusing but try macro recording a new
connection, and have a look at that .Querystring value.

Peter Jamieson"Andy" wrote in message

...



Hi Peter,

Thanks that is exactly what I need. I can now merge the reocrds that I
need.
Sorry to be a pain but there is one more thing. I the merge I am creating
is
something that will run regularily from a Excel sheet. I would therefore
like
to create a Macro so that when the time comes all the user has to do is to
press a button and the merge is done automatically. Now I have set this up
for another tab of the spreadsheet but that was using all the records.
When I
do the same thing, the Macro doesn't seem to pick up that I have selected
certain records, even when (During the recorsing of the Macro) I deselect
all
the records, go into Advanced options, select the records I need and then
proceed. When I run the Macro it just picks up all the records in that
sheet.
Is that normal?? or am I doing something completely wrong.

Thanks again for your help it is really appreciated

"Peter Jamieson" wrote:

You may not need a macro if the seelction criteria are simple enough. In
Word 2002/2003/2007 you can display the Edit recipients box and either
select records based on their content using the dropdown at the top of
each
column, or click the "Advanced options" in the dropdown and set up the
conditions in the Query Options dialog box that appears.

You would then need to save the Mail Merge Main document with those
options.
When yo re-open it, they should still be there, but problems have been
reported in this area in the past.

If that isn't enugh, maybe we can alk about a macro.

Peter Jamieson
"Andy" wrote in message
...
Wondering if you can help, I'm kind of learning this as I go along so
apologies if this is a really dumb question.

I need to setup a mail merge that will use a specific selection from an
Excel Sheet. For example everything under the column "University" that
contains "LBS" needs to be merged. I can do this manually by either
selecting
the right records when I mail merge, or create a new spreadsheet
containing
only the records I need. But I want to create a wonderful Macro that
does
it
all for me. Is there a way to create a a macro that will look at a Tab
on
a
Spreadsheet then look at a specific column and merge only the records
with
a
certain entry. I hope that makes sense...

Thanks all- Hide quoted text -

- Show quoted text -

I have been using the advance filtering of Excel records for a while
to select recipients for Work email merge. I have two questions.
First, why doesn't Word remeber the source file? I have to reconnect
every time. Second, whensetting up advanced filters, I use two field
with selection criteria of "not blank" joined by AND. Some times these
criteria get duplicated, maybe when I save the Word file and the
operator is changed to OR. Frustrating!