Merge changes data to 0's

[v-ger]

In a Word doc, merging simple Excel (2003) data (Address, ZIP, etc.) one of
my field codes inserts the number 0 in the doc rather than the data I have in
the column when it is a complicated number (e.g., 24-B). Where I have simple
numbers (single or multiple digits - doesn't matter) it inserts the data just
fine. I can view which ones are going to be changed to 0's when the "Mail
Merge Recipients" window pops up during the merge.

I have changed the cell formatting for the entire column to numbers,
general, text, etc. but that hasn't helped. If I import it into a Word text
file w/table that will fix it, but the Excel wksheet is under constant
revision week to week, so importing each time isn't a good workaround.

Does anyone know how and why the merge changes my data? Why does it change
just the records that aren't simple numbers? Word & Excel 2003 on XP Pro,
merging to single page doc from single worksheet, about 250 merge records /
approx 10 columns. I did not create either the doc or the spreadsheet
myself, but inherited them from someone who is having this issue in hopes
that I might help them - but I cannot figure it out. Please, if you know
what the issue is...

Thank you very much!

 

[Peter Jamieson]

The basic problem is that when you connect to Excel using the default method
in Word 2003 (OLEDB), the OLEDB provider tries to determine what kind of
data is in each column. If the first few rows are numeric, it decides that
the column is numeric, then deals with non-numeric values the way you
describe.

Workarounds?
a. Revert to the DDE connection method (requires Excel to be present).
Check Word Tools|Options|Genereal|Confirm conversion at open, reconnect and
choose the DDE method when offered. You will only be able to access the
sheet you last opened.
b. If there are fewer than 63 (or so) columns, copy/paste your Excel sheet
into a Word document and use that as the data source.
c. (Maybe) Insert about 25 columns containing text-only data in the
required column at the top of the sheet, reconnect, eliminate those records
when you merge.

Peter Jamieson

 

[v-ger]

Hey Jamieson! You're a genius! The first option you gave worked like a
charm. Thank you so much. Really, I tried everything I knew...I just didn't
know enough! I can't even find this info in my 900-page Word 2003 manual, or
with a hundred different Google searches. Thanks for helping me out.

Can I ask you one more question, please? Can you please tell me how you
learned all of this? There are so many questions about Word that are beyond
what the books cover and the old "trial-and-error" on my part usually results
in just "error". I need better resources and am willing to spend or rip.
Thanks.

v-ger

 

[Peter Jamieson]

Glad it worked.

To try to answer your points/questions...

Probably the best resource for finding answers, or even pointers to answers,
is to search these newsgroups. Personally I prefer to do it via
http://groups.google.com. Most of the stuff I know about mailmerge and
fields has been posted in this group, for example, in some cases, many, many
times.

Books oriented towards "end users" typically cover whatever the authors
perceive are the most-used features in a product, except for books which
specifically cover "gotchas." I assume that a product like Word is not
generally regarded as "data-oriented" and so Mailmerge is typically reduced
to an explanation of what it is and some simple walkthroughs. I think that
is a pity because there are som many "gotchas" in mailmerge that plenty of
users will not be satisfied by the out-of-the-box features. Also, writing
about features that rely on interoperability is inherently difficult,
because both the author and the reader typically have to understand a fair
amount about other application involved, and the number of version-related
issues potentially skyrockets. There aren't many "technical" books about
Word that cover this area in depth - in fact the only two that spring to
mind are both in German and authored by Cindy Meister, myself, and others.
Have a look at http://www.amazon.de if you are interested!

The vast majority of my knowledge, such as it is, comes from trial and error
on my part. Some of it is from reading contributions by other people,
especially when someone undermines a long-held belief.

The main criteria for learning stuff by trial and error in my experience are
a. be willing
b. have the necessary tools (e.g. you can't really deduce stuff about Word
97 without having a copy to hand)
c. know what you are trying to solve or discover
d. be wary of "it can't be done" assertions unless they come from someone
you trust to get that right
e. don't let preconceptions override the evidence of your own eyes (it is
rare, but not unknown, for people in this group to reject a suggestion
because they "can't see how it could work" or some such. My approach is "see
if it works, even if it seems weird, then try to work out why."
f. be tenacious, but also aware of the Law of Diminishing Returns.

 

[v-ger]

Noted! ...and with much gratitude.

Looking forward to future problems & issues, v-ger.