Intermittent merging of number fields...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 21 Apr 2004 18:13 GMT

Hello,

I am experiencing a problem with one merged field in my Word document
using Excel as the data source. I am on WIndows XP, using Office 2003.

Some of the numbers merge into the document and some do not. I have
not modified the mergefield code at all. The mergefield is called
"account_". I have formatted the Excel data range as number, as text,
as general, and none of them seem to work. Of the numbers that do
not merge, some are quite long - over 20 characters, and some contain
letters and dashes as well as numbers.

Is there some switch or code that I can add to the Word merge field?

I'm at wits end, you help is appreciated.

Michael

Reply to this Message

Doug Robbins - Word MVP - 22 Apr 2004 10:29 GMT

Hi Mike,

Can you copy into a message some of the numbers that will not merge so that
we can see what they are and try it.

Signature

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

> Hello,
>
[quoted text clipped - 13 lines]
>
> Michael

Reply to this Message

Mike - 22 Apr 2004 12:02 GMT

Doug - here is a little more info on my situation.

In Excel, I have one sheet that pulls data from several other sheets.
The Word doc then merges against the main sheet in Excel (the "data"
sheet). On the Word doc, the field I am having an issue with is
pulled from the data sheet in Excel - that column is populated by
doing a VLOOKUP. Here are a few of the numbers causing me problems:
0230000058869050000E
ABA#011001234 DDA#109819 (this is one 'number')
600810303001
46000X

Again, the issue is only on certain rows, not all rows. All the
formatting looks the same, and I've re-entered the data into the other
excel sheets...

Thanks.

> Hello,
>
[quoted text clipped - 13 lines]
>
> Michael

Reply to this Message

Cindy M -WordMVP- - 22 Apr 2004 17:59 GMT

Hi Mike,

This is happening due to the way EXCEL converts the data through it's
ODBC driver, and OLE DB (the new default connection method) uses that
driver for Excel to get the merge data. The ODBC driver defines data
types for the Excel columns (fields) based on the contents of the first
8 rows. So if the first entries appear numeric, the data type is set as
numeric and any entries that aren't numeric aren't picked up by OLE DB.

You can either
- go back to an older connection method (how to do this is described in
the Word 2002 section of my website's MailMerge FAQ)

- explicitly set the data type of the column in Excel to "text". Note
that this can't be done using the Format tools, as these only affect the
display in Excel, not how the data is stored. You have to select the
column, then use Data/Text to column

> I am experiencing a problem with one merged field in my Word document
> using Excel as the data source. I am on WIndows XP, using Office 2003.
[quoted text clipped - 7 lines]
>
> Is there some switch or code that I can add to the Word merge field?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail :-)

Reply to this Message