> 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" <A...[ at ]discussions.microsoft.com> wrote in message
>
> news:85AD020B-CA3C-4E48-A99D-CF14F6AD0A9C[ at ]microsoft.com...
>
>
>
> > 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" <A...[ at ]discussions.microsoft.com> wrote in message
> >>news:EE6E43CD-4720-4807-9DB8-6D2E0054C7CE[ at ]microsoft.com...
> >> > 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 -