Access 2002 OutPutTo to Excel 2002 truncates memo

[Virginia Kirkendall]

I've read that DoCmd.OutPutTo defaults to Excel 95/97 (WHY?) which
only allows for 255 chr in text formatted fields. I understand why
then that DoCmd.OutputTo with excel specified as the format will
produce this truncate "feature." So, why then do I get the same
truncated text when I take out the file type from outputto? The OutPut
To file type selections appear I choose Excel 2002 and get the same
results.

Of course, transferspreadsheet does not truncate the memo fields, but
I am interested in outputting reports to save the grouping levels in
Excel as well.

Does anyone have any idea why this happens? Thanks very much, Ginger

 

[John Nurick]

Hi Ginger,

DoCmd.OutputTo with acFormatXLS produces an Excel 5.0/95 file (not
95/97). As for why, the answer seems to be that the the code hasn't been
updated since Excel 95 (=Excel 7.0).

When in Access 2002 I omit the Format argument from DoCmd.OutputTo, I
get a dialog offering among other formats "Microsoft Excel", "Microsoft
Excel 5-7" (= 5.0/95), and "Microsoft Excel 97-2002". As far as I can
make out, the second of these produces the same results as using
acFormatXLS, while the first and last produce files in Excel 97 format.
But that's just the file format: the data is the same, presumably
because they all use the same OutputTo code which is predicated on the
old format.

I've read that DoCmd.OutPutTo defaults to Excel 95/97 (WHY?) which
only allows for 255 chr in text formatted fields. I understand why
then that DoCmd.OutputTo with excel specified as the format will
produce this truncate "feature." So, why then do I get the same
truncated text when I take out the file type from outputto? The OutPut
To file type selections appear I choose Excel 2002 and get the same
results.

Of course, transferspreadsheet does not truncate the memo fields, but
I am interested in outputting reports to save the grouping levels in
Excel as well.

Does anyone have any idea why this happens? Thanks very much, Ginger

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Virginia Kirkendall]

When in Access 2002 I omit the Format argument from DoCmd.OutputTo, I
get a dialog offering among other formats "Microsoft Excel", "Microsoft
Excel 5-7" (= 5.0/95), and "Microsoft Excel 97-2002". As far as I can
make out, the second of these produces the same results as using
acFormatXLS, while the first and last produce files in Excel 97 format.
But that's just the file format: the data is the same, presumably
because they all use the same OutputTo code which is predicated on the
old format.

Hmmm. I still don't get it. When I output a text field that is larger
than 255 chr to the choice "Microsoft Excel 97-2002" the cell is
truncated. I haven't tried plain Microsoft Excel. Doesn't the file
format determine the length of the text field in Excel? Excel 5-7 had
a 255 chr limit, whereas 2002 has a much higher limit? Thanks much,
Ginger

 

[John Nurick]

Hmmm. I still don't get it. When I output a text field that is larger
than 255 chr to the choice "Microsoft Excel 97-2002" the cell is
truncated. I haven't tried plain Microsoft Excel. Doesn't the file
format determine the length of the text field in Excel? Excel 5-7 had
a 255 chr limit, whereas 2002 has a much higher limit? Thanks much,
Ginger

My guess is that there's a 255-character limit hard-coded into the
OutputTo code (which appears to be virtually unchanged since Access 95).

Another possibility, which I've only just remembered, is this. You *say*
"a text field larger than 255 chr" but there's no such thing. Are you in
fact exporting text generated by an expression in a calculated field in
a query (or a memo field with a format applied to it)? If so, there are
circumstances in which Access assumes that this is a Text field and
truncates it at 255 characters.


John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.