Express mail merges--how to suppress Excel records?

[DanE]

Office XP Standard 2002; Win98

I've implemented Suzanne Barnhill's epistle to restore the
old ways of doing mail merge--with the tool bar instead of
the Mail Merge Wizard. I'm merging with a large Excel
data source and I'd like Word to load only limited records
with each merge. However, when I plug in a named group of
cells into the "Entire Spreadsheet?" dialog box, Word
stills loads the entire data source. Why doesn't this
work?

Also, how does one permanently link a Main Document with a
Excel data source (using the Mail Merge tool bar) so that
link doesn't have to be established each time?

Thanks,
Dan

 

[Doug Robbins - Word MVP]

I assume that you have the "Confirm conversions at open" box checked under
Tools>Options>General.

If you then choose either of the following methods:

MS Excel Worksheets via DDE (*.xls)
Excel Files via ODBC (*.xls)

then you select the named range that you want to use, my experience when I
then use the View Mail Merge Recipients dialog is that only the records in
that named range are loaded. Note that the first row in the range will be
taken as containing the mailmerge field names.

If you save the mailmerge main document after attaching the datasource, the
datasource should automatically be attached when you re-open the main
document.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Guest]

I assume that you have the "Confirm conversions at open" box checked under
Tools>Options>General.
Yes.

If you then choose either of the following methods:

MS Excel Worksheets via DDE (*.xls)
Excel Files via ODBC (*.xls)

I use MS Excel Worksheets via DDE (*xls)

then you select the named range that you want to use, my experience when I
then use the View Mail Merge Recipients dialog is that only the records in
that named range are loaded.

That's what I would expect and hoped for. However, all
records are loaded whether one uses the Mail Merge Wizard
(where the Mail Merge Recipients dialog appears) or the
Merge Tool Bar where one selects record numbers to be
merged. (Please note that in Suzanne Barnhill's epistle
she points out in footnote #5..."For very large databases
you will definitely want to avoid the Mail Merge
Recipients dialog entirely, for two reason. One is that
the dialog is very slow to open if your data source has
many records. The other is that a data source with more
than 286 fields will crash Word when you try to display
them in this dialog." This is the reason I'm using the
Merge Tool Bar. However, all records are loaded using
either procedure.

If you save the mailmerge main document after attaching the datasource, the
datasource should automatically be attached when you re- open the main
document.

Again, that's what I would like to have happen. However,
using the Merge Tool Bar one must select the Open Data
Source button in order for the Merge button to become
active. Selecting the Open Data Source button, of course,
kicks off the off process of finding the data source,
choosing the linking method, naming a group of cells to
merge (which apparently doesn't work) or the entire
spreadsheet, and loading every single record.

Any additional help or insights would be appreciated.

Dan

 

[Doug Robbins - Word MVP]

My experience differs from yours on all counts.

After the data source is attached to the document and the document is saved.
If it is then closed and re-opened, it has the data source already attached
to it and the merge can be executed immediately.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Guest]

My experience differs from yours on all counts.

Is that with the Tool Bar or the Merge Wizard?

How do I contact Susanne Barnhill to get her insight?

Thanks,
Dan

 

[Graham Mayor]

How do I contact Susanne Barnhill to get her insight?

I think she is away at the moment, but a post in the newusers group will
find her. Like most of us who service these groups she is unlikely to
appreciate unsolicited direct contact.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>><

 

[DanE]

I think I have resolved both issues, while using the Mail
Merge Tool Bar, of (1) avoiding loading all Excel records
and (2) making a permanent datasource link: The
dialogue "Opening this document will run the following SQL
Command" requires a "yes" response in order to avoid all
manner of problems addressed above. I was previously
answering "no" because this answer was part of some other
routine previously tried to make Mail Merge work. I
suppose for us non-professional Word users trying every
possible combination and permutation of buttons, settings,
and dialogue boxes will eventually make things work.
However, it makes me want to take Bill out and shake him.

 

[Doug Robbins - Word MVP]

Of course it helps if you give us all of the information in the first place.

See this Knowledge Base article

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document - 825765

http://support.microsoft.com?kbid=825765


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP