Character Limitation

[ntwichell]

I have a word template document and I am trying to merge data from an
Excel spreadsheet.. I have a couple of large text fields and the merge
seems to be truncating at 255 characters.. Does anyone know if there is
a limitation? if so can you link me to the documentation.

Nance

 

[Peter Jamieson]

Your best short-term bet, assuming you have Excel on your system, do not
have non-ANSI Unicode characters in your Excel sheet, and have the data in
the first sheet of your workbook (and probably other stuff as well...), is
to try changing the way Word gets your data to the old method (DDE): check
Word Tools|Options|general|"Confirm conversion at Open", go through the
connection process again, and select the DDE method in the additional dialog
box.

Assuming you are using Word 2002/2003, the problem is that the OLEDB
provider that Word uses to get data from Excel tries to determine a data
type for each column. It looks in the first 8 or 25 rows and uses the data
type it finds for the rest of the column. When the types are mixed, it takes
the "majority type". The trouble is that it regards text less than 256
characters long as "text" type and longer ones as "memo" type, and there are
also additional special rules when dealing with texts and memos. You /might/
find, for example, that by adding enough trailing space to shorter texts in
the same column, the whole thing springs to life again.

I'd point you to the web pages I'm writing on this but there are so many ifs
and buts that it's taking me forever to complete.

Peter Jamieson

 

[Peter Jamieson]

Is this the same issue that's truncating the parmdesc string in my code

below
to 255 characters?
Probably.

No way around it at all?

If you've tried DDE and that doesn't work (or you can't use DDE because you
don't want Excel popping up etc., and if you cannot pad the texts as I've
suggested (or it doesn't work) then the answer is
"probably not".

It /is/ possible to tweak the ODBC/OLEDB driver/provider in one or two ways
by changing the connection string and a registry entry, but I've already
taken account of that in my previous response - i.e. as far as I know there
is no set of tweaks that guarantees to get you /memo/ fields if they are
mixed with text fields in the same column.

If you can't sort it out using the ODBC/OLEDB driver/provider then as far as
I can see you either have to know how to access .xls files directly (in
which case we're talking about a completely different ball game) or you have
to automate Excel in order to get the data into a format that works.
However, at that point I have to research the problem further myself - the
format most likely to work is a table in a Word document, and that is only
workable if you have no more than 63 columns. You may find that long texts
in Access or SQL Server tables are equally problematic when used as merge
data sources).

I find it quite irritating that "255" is still such a barrier - the bar
could so easily have been raised to "65535" (at least) a long time ago.

Peter Jamieson

 

[Peter Jamieson]

Is this the same issue that's truncating the parmdesc string in my code
I just checked this again and your problem seems actually to be unrelated -
the problem here seems to be the more general one that the "strings" you can
pass to some of these method calls and the strings returned by them are not
true VBA strings, which can be very long, but strings with a 255-character
(or byte) limit.

Peter Jamieson

 

[Peter Jamieson]

<<
Assuming you are using Word 2002/2003, the problem is that the OLEDB
provider that Word uses to get data from Excel tries to determine a data
type for each column. It looks in the first 8 or 25 rows and uses the data
type it finds for the rest of the column. When the types are mixed, it takes
the "majority type". The trouble is that it regards text less than 256
characters long as "text" type and longer ones as "memo" type, and there are
also additional special rules when dealing with texts and memos. You /might/
find, for example, that by adding enough trailing space to shorter texts in
the same column, the whole thing springs to life again.
Actually, this is not quite true. By default, when the types are mixed, it
takes the "text" type, unless the types are "memo" and "text", in which case
it does appear to use "memo", at least in my copy of Office, on my system,
right now. But
a. every single cell in the relevant column in the first 25 non-heading
rows needs to have a memo or text in it - no numbers
b. I am fairly sure from previous experiments that even that is not always
enough.

Peter Jamieson