[Andy]

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

[Peter Jamieson]

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

[Andy]

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

[Peter Jamieson]

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

[[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!

[Peter Jamieson]

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.

Well, I don't know for sure, and there was definitely at least one problem
in WOrd 2002 that was fixed in Word 2003, but I believe what happens is
this:
a. when you connect to an Excel workbook in Word 2002 (XP) or later, Word
uses OLE DB by default
b. when it does that, it creates an OLE DB "Connection String" that
includes the full path name of the workbook
c. however, at some point it truncates that connection string to 255
characters (it isn't immediately, at least not in Word 2003, but it may do
so when it reopens the document
d. although it all worked the first time you opened the data source, when
Word next opens it, it tries to use the truncated string, which has either
truncated the path or is otherwise invalid, and the connection fails.

You can probably test that hypothesis fairly simply by putting the same
sheet in two folders, one with a very short name and the other deep inside a
hierarchy.

I had hoped that it might be possible to work around this problem by using a
..odc file to point to the Excel file, and either relying on Word to go via
the .odc each time you open the Word document, or paring down the length of
the Connection string as far as possible (because there's plenty of stuff
that doesn't need to be in it). However, even if you open via a .odc, once
you have opened the data source, the next time WOrd goes straight to that
data source - it doesn't open what the .odc tells it to open. Also, Word
always pads out the connection info. you provide with the default values of
all the OLE DB connection parameters for this driver.

Of course, I may be swrong about my hypothesis and there may also be other
things going on, but that's my best guess.

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!

Yes, and again, it's an error in Word. All you can do is construct the query
string yourself and try to prevent Word from doing so if necessary.

Of course if you ever acquire a brand new copy of Word there's nothing to
stop you reporting these problems on a free technical incident, but MS
should know about these problems by now and it's really a question of
commercial choices as to what they do about them.

Peter Jamieson
Peter Jamieson

wrote in message
ps.com...
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!