exporting to excel

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

One of the ways I backup my flat (no relational databases)
microsoft access file is by bringing up the TABLE and exporting
it as a "Microsoft Excel 97-2000 (*.xls) type.

It seems to work okay, except that I have a memo field that
is as long as a few hundred characters for some of the
entries. When I examine the resultant excel file, some of the
larger memo field entries have been truncated.

How can I solve this?

Gary
(e-mail address removed)
 
You can't. Excel doesn't allow more than 255 characters in a cell.
Click to expand...

Recent versions of Excel can store up to 32767 characters in a cell,
though only 1024 can be displayed in the worksheet (as opposed to the
formula bar). I don't have Access 2000, but in Access 2002 have no
problems exporting memo fields containing thousands of characters to
Excel (97-2002).

Exporting to Excel 5-7 or earlier formats causes the memo fields to be
truncated, however, as does exporting to later formats with "Save
formatted" checked.

Exporting by using "Analyze it with Excel" (or DoCmd.OutputTo) also
seems to use code that assumes the earlier Excel file formats.

Also, if you export a query that contains an expression that results in
more than 255 characters, the result is truncated to 255 characters in
the export file. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;207668

When Access exports a report to Excel it also seems to use the old Excel
5.0 format and consequently truncates memo fields:
http://support.microsoft.com/default.aspx?scid=kb;en-us;294286
For a separate memo field/report/export to Excel problem see:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;281153
 
John Nurick said:
Recent versions of Excel can store up to 32767 characters in a cell,
though only 1024 can be displayed in the worksheet (as opposed to the
formula bar). I don't have Access 2000, but in Access 2002 have no
problems exporting memo fields containing thousands of characters to
Excel (97-2002).
Click to expand...

Guess I'm going to have to upgrade one of these days, aren't I? <g>

Sorry for any confusion I may have caused, Gary.
 
Hi John.

I couldn't see the original question but I'm wondering if
you could confirm that your response explains my problem,
which I'll explain below?

I'm using Access 97 and Excel 97, both are SR-2.

I have given users the ability to preview reports. Very
simple to do. I have since discovered they like to export
the previewed reports to an Excel spreadsheet (they
choose 'Microsoft Excel 97' from the Save as type drop
down).

This appears to work fine except that when they go to
open the spreadsheet in Excel 97, they get a message
saying the file was created in a previous version of
Microsoft Excel and are asked if they want to update it
to the new format.

I've been asked to correct this (or at least explain why
it is happening).

- Mike

-----Original Message-----
You can't. Excel doesn't allow more than 255 characters
Click to expand...
in a cell.

Recent versions of Excel can store up to 32767 characters in a cell,
though only 1024 can be displayed in the worksheet (as opposed to the
formula bar). I don't have Access 2000, but in Access 2002 have no
problems exporting memo fields containing thousands of characters to
Excel (97-2002).

Exporting to Excel 5-7 or earlier formats causes the memo fields to be
truncated, however, as does exporting to later formats with "Save
formatted" checked.

Exporting by using "Analyze it with Excel" (or DoCmd.OutputTo) also
seems to use code that assumes the earlier Excel file formats.

Also, if you export a query that contains an expression that results in
more than 255 characters, the result is truncated to 255 characters in
the export file. See
http://support.microsoft.com/default.aspx?scid=kb;en- us;207668

When Access exports a report to Excel it also seems to use the old Excel
5.0 format and consequently truncates memo fields:
http://support.microsoft.com/default.aspx?scid=kb;en- us;294286
For a separate memo field/report/export to Excel problem see:
http://support.microsoft.com/default.aspx?scid=kb;EN-
Click to expand...
US;281153

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Mike,

AFAIK this is just a historical curiousity, like beetroot in Australian
hamburgers, and doesn't do any harm unless there are long strings that
get truncated.

Tell the users to enjoy this fine tradition while they can (unless of
course they control the budget, in which case persuade them to upgrade
you to Office 2003<g>).


Hi John.

I couldn't see the original question but I'm wondering if
you could confirm that your response explains my problem,
which I'll explain below?

I'm using Access 97 and Excel 97, both are SR-2.

I have given users the ability to preview reports. Very
simple to do. I have since discovered they like to export
the previewed reports to an Excel spreadsheet (they
choose 'Microsoft Excel 97' from the Save as type drop
down).

This appears to work fine except that when they go to
open the spreadsheet in Excel 97, they get a message
saying the file was created in a previous version of
Microsoft Excel and are asked if they want to update it
to the new format.

I've been asked to correct this (or at least explain why
it is happening).

- Mike

-----Original Message-----
You can't. Excel doesn't allow more than 255 characters
Click to expand...
in a cell.

Recent versions of Excel can store up to 32767 characters in a cell,
though only 1024 can be displayed in the worksheet (as opposed to the
formula bar). I don't have Access 2000, but in Access 2002 have no
problems exporting memo fields containing thousands of characters to
Excel (97-2002).

Exporting to Excel 5-7 or earlier formats causes the memo fields to be
truncated, however, as does exporting to later formats with "Save
formatted" checked.

Exporting by using "Analyze it with Excel" (or DoCmd.OutputTo) also
seems to use code that assumes the earlier Excel file formats.

Also, if you export a query that contains an expression that results in
more than 255 characters, the result is truncated to 255 characters in
the export file. See
http://support.microsoft.com/default.aspx?scid=kb;en- us;207668

When Access exports a report to Excel it also seems to use the old Excel
5.0 format and consequently truncates memo fields:
http://support.microsoft.com/default.aspx?scid=kb;en- us;294286
For a separate memo field/report/export to Excel problem see:
http://support.microsoft.com/default.aspx?scid=kb;EN-
Click to expand...
US;281153

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Hi John.

I assume from your message that my understanding of the
problem was correct. Yes?

Also, what does AFAIK mean?

- Mike
-----Original Message-----
Hi Mike,

AFAIK this is just a historical curiousity, like beetroot in Australian
hamburgers, and doesn't do any harm unless there are long strings that
get truncated.

Tell the users to enjoy this fine tradition while they can (unless of
course they control the budget, in which case persuade them to upgrade
you to Office 2003<g>).


Hi John.

I couldn't see the original question but I'm wondering if
you could confirm that your response explains my problem,
which I'll explain below?

I'm using Access 97 and Excel 97, both are SR-2.

I have given users the ability to preview reports. Very
simple to do. I have since discovered they like to export
the previewed reports to an Excel spreadsheet (they
choose 'Microsoft Excel 97' from the Save as type drop
down).

This appears to work fine except that when they go to
open the spreadsheet in Excel 97, they get a message
saying the file was created in a previous version of
Microsoft Excel and are asked if they want to update it
to the new format.

I've been asked to correct this (or at least explain why
it is happening).

- Mike

-----Original Message-----
On Sun, 1 Feb 2004 15:42:29 -0500, "Douglas J. Steele"

You can't. Excel doesn't allow more than 255
Click to expand...
characters
in a cell.
Recent versions of Excel can store up to 32767 characters in a cell,
though only 1024 can be displayed in the worksheet (as opposed to the
formula bar). I don't have Access 2000, but in Access 2002 have no
problems exporting memo fields containing thousands of characters to
Excel (97-2002).

Exporting to Excel 5-7 or earlier formats causes the memo fields to be
truncated, however, as does exporting to later formats with "Save
formatted" checked.

Exporting by using "Analyze it with Excel" (or DoCmd.OutputTo) also
seems to use code that assumes the earlier Excel file formats.

Also, if you export a query that contains an
Click to expand...
expression
that results in
more than 255 characters, the result is truncated to
Click to expand...
255
characters in
the export file. See
http://support.microsoft.com/default.aspx?scid=kb;en- us;207668

When Access exports a report to Excel it also seems to use the old Excel
5.0 format and consequently truncates memo fields:
http://support.microsoft.com/default.aspx?scid=kb;en- us;294286
For a separate memo field/report/export to Excel
Click to expand...
problem
see:
http://support.microsoft.com/default.aspx?scid=kb;EN- US;281153

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top