Help merging worksheets with more than 255 characters...

[Greg]

Hi,

I am working on a project which requires mail merging an Excel
spreadsheet with several fields that contain more than 255 characters.
I am using Word 2003 on an XP machine. The results from these merge
fields are truncated. I found a workaround for this problem in the
online Microsoft Word Support file, WD2000: Field Text Truncated When
Merging or Inserting Database. It recommended I use Dynamic Data
Exchange (DDE) instead of Open Database Connectivity (ODBC). However,
this workaround doesn’t solve my problem, the fields remain
truncated.

Are there any other alternatives to resolve this problem? It seems
converting my data to CSV or Tab delimited causes the data to be
truncated as well. I remember reading somewhere that I could convert
my Excel spreadsheet to an Access database which doesn't care about
this 255 character limitation. However, I didn't have much success
importing these fields in Access.

Thanks in advance for any assistance you can provide.

Regards,
Greg

 

[Peter Jamieson]

At least some aspects of this problem are described at
http://tips.pjmsn.me.uk/t0003.htm, but as you will see it is almost
impossible to create a catch-all solution based on Excel unless
(perhaps) you can control what is in the first 8 rows of the sheet.

If you /have to/ start from an Excel sheet then importing into Access
will probably require automation of Excel (you can actually create a
linked table in Access that gets its data from Excel, but that will
suffer from the same problems as Word does because it also uses
ODBC/OLEDB to get the data). But if you are going to automate Excel, I
would consider transferring the data to a table in a Word document and
using that as your data source (or perhaps trying to "fix" the data in
the first 8 rows).

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Shane Devenshire]

Hi,

You might get a more satisfactory answer in one of the Word newsgroups, just
a thought,

Cheers,
Shane Devenshire

 

[Suzanne S. Barnhill]

Note that the message was cross-posted to three NGs, two of which are for
Word.

 

[Greg]

Hello Peter,

Thanks for your response it was very helpful in figuring out how to
create a workaround for my particular problem! After reading through
the "Word: Problems with Excel data sources" <http://tips.pjmsn.me.uk/
t0003.htm> weblink I had success importing my Excel document into
Access.

Key factors contributing to my success include keeping Excel Open
while I Copy/Paste into Word. This created a Word table file which I
then saved. Next I was able to import this saved Word document into
Access as a Table without any errors. However, I was surprised to
learn once my information was safely inside Access, the order of my
records had changed. I simply created a new Access query sorting on my
required fields and used this query to perform a new Mail Merge in
Word. The merged results contains all information in every field
including those larger than 255.

Kind regards,
Greg

 

[Peter Jamieson]

Hi Greg,

Thanks for the feedback.

FWIW I was able to copy/paste my Excel rows into the "spreadsheet view"
of a new Access table directly - in Access 2007 it seems to want to use
an AutoIncrement field called ID as the first column, but otherwise the
process worked reasonably smoothly once you'd worked out exactly what to
do. So that might be a viable manual route that cuts out the middle man
(Word). However,
a. haven't done that with earlier versions of Access for a while
b. don't know if it will retain > 255 strings in all cases
c. I wouldn't really want to try automating that!


Peter Jamieson

http://tips.pjmsn.me.uk