problem with merging 97 to 2003/xp

[BCHousing]

We're trying to do a mail merge in Word 2003. The data source is an excel spreadsheet that was created in office 97. The merge works fine in Office 97, but when we try it in Office 2003 it seems to truncate the field names it is retrieving from the excel spreadsheet. We've tried saving the spreadsheet in excel 2003 format to see if that would help, but no go. What happens is that when you start the merge process word complains it can't find the field, and so you then have to select the correct field from the list it provides. That might not be so bad, except we have LOTS of documents that are used regularly. To go through each docuemnt to correct it would take a lot of time. Any ideas?

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?QkNIb3VzaW5n?=,

We're trying to do a mail merge in Word 2003. The data source is an excel spreadsheet that

was created in office 97. The merge works fine in Office 97, but when we try it in Office
2003 it seems to truncate the field names it is retrieving from the excel spreadsheet. We've
tried saving the spreadsheet in excel 2003 format to see if that would help, but no go. What
happens is that when you start the merge process word complains it can't find the field, and
so you then have to select the correct field from the list it provides.Do you have any idea whether these merge documents are linked to the Excel sheets using DDE,
OLE DB or ODBC. This definitely was a change in Word 2002, but one I usually associate with
OLE DB...

Have you tried activating "Confirm conversions on open" in Tools/Options/General, then linking
in the spreadsheet using DDE (the old default data connection method)?

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

 

[BCHousing]

Thanks for the reply. The problem seems to be caused by punctuation in the fields in Excel.

This is what one of the fields currently looks like. Location City: (eg: Nanaimo & Ladysmith) (Text)

This is what Word wants to see Location_City_eg_Nanaimo__Ladysmith_Text

It would be rather time consuming to have to rename all the fields. I'm hoping their is a way we can have Word accpet the fields as they are, or find an easier way to reane the fields.

I've tried the confirm conversion on open, but it did not seem to help. We tried renaming all the fields using search and replace to eliminate the punctuation, but it still seemed to truncate the field names. 40 characters seems to be were it is truncating the field name.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?QkNIb3VzaW5n?=,

The problem seems to be caused by punctuation in the fields in Excel.

This is what one of the fields currently looks like. Location City: (eg: Nanaimo & Ladysmith) (Text)

This is what Word wants to see Location_City_eg_Nanaimo__Ladysmith_Text

It would be rather time consuming to have to rename all the fields. I'm hoping their

is a way we can have Word accpet the fields as they are, or find an easier way to reane
the fields.I'm pretty sure you won't find a way to get Word to accept the field names as they stand,
with the punctuation. And 40 characters is now the limit.

Excel has a "comment" feature that you may not be familiar with. How about putting the
e.g. stuff into a cell comment, so that it will display when the user hovers the mouse
over the little comment tag in the top corner of the cell?

I've tried the confirm conversion on open, but it did not seem to help. We tried

renaming all the fields using search and replace to eliminate the punctuation, but it
still seemed to truncate the field names. 40 characters seems to be were it is
truncating the field name.
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

 

[BCHousing]

Ok, the comment field is a good feature. No punctuation, I can live with as well. Why no spaces though? I have a good idea of what the problem is now though, and the steps we need to take to fix it.

Any idea of why they changed the the field size, and removed all these features that were allowed in Word 97?

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?QkNIb3VzaW5n?=,

Ok, the comment field is a good feature. No punctuation, I can live with as well.

Why no spaces though? I have a good idea of what the problem is now though, and the
steps we need to take to fix it.Well, the basic technology behind mail merge is about 15 years old, and spaces just
weren't valid for field names back in those days. They were sometimes used as field
delimiters (instead of a tab or comma) and were also used to fill out "fixed width"
fields, such as many main-frame databases used. Generally, the workaround is to use an
underscore where you'd want a space...

Any idea of why they changed the the field size, and removed all these features that were allowed in Word 97?

I've never been told explicitly, no. But I think it may have something to do with Word's
not liking long strings of text when trying to manage a mail merge connection.
Especially when you want to set criteria, Word's limited to an absolute max of 512
characters for the SQL (SELECT Field1, Field2, Field3 FROM MyTable Where Field1="John
Smith") when connecting, and 256 within Word. If you have really long field names you
can't set many criteria. And I suspect one of the most reported failure messages is
"Cannot open data source".

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