Memo Text Truncation during Excel Export

[Taffman]

I've searched this goup for an answer to this, there are many
discussions that come close but non that I can find that actually
addresses this particular problem.

I'm exporting queries to Excel. These queries have memo fields in them
Each memo field is trunkated, i.e cuts off after 255 characters.

Is there any way either via VBA or otherwise to get all my memo text to
export to Excel.
I note that there is no such limit when exporting to RTF, but I need
the Excel format.

Any help would be much appreciated, apologies if this has already been
discussed and answered.

[Allen Browne]

Does this article help:
Memo Field Truncated When Report Is Output to Excel
at:
http://support.microsoft.com/default...b;en-us;208801
It suggests specifying a more recent Excel format.

If that doesn't help, does the query itself show more than 255 characters?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Taffman" <Da**********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I've searched this goup for an answer to this, there are many
discussions that come close but non that I can find that actually
addresses this particular problem.

I'm exporting queries to Excel. These queries have memo fields in them
Each memo field is trunkated, i.e cuts off after 255 characters.

Is there any way either via VBA or otherwise to get all my memo text to
export to Excel.
I note that there is no such limit when exporting to RTF, but I need
the Excel format.

Any help would be much appreciated, apologies if this has already been
discussed and answered.

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43**********************@per-qv1-newsreader-01.iinet.net.au:

Does this article help:
Memo Field Truncated When Report Is Output to Excel
at:
http://support.microsoft.com/default...b;en-us;208801
It suggests specifying a more recent Excel format.

If that doesn't help, does the query itself show more than 255
characters?

I ran onto this problem the other day exporting to tab-delimited
text (for import into a MySQL database on a website). I discovered
something new about saved export specs, that the column types are
there, but hidden, like with hidden columns in datasheets. By
default the export spec datasheet lists only If you put your mouse
cursor over the right-hand edge of the single field name column and
drag, you can open up the other columns (the ones you'd see in an
import spec). Actually, you have to put the mouse over to the right,
a bit away from the edge of the single column header. With fiddling
around like this, you can eventually see the data type column, and
you'll see that it's likely set to TEXT instead of to MEMO.

An esier way of fixing this is to do an export, then start an import
from what you've just exported. Load the saved export spec, and
you'll be able to see the data types more easily there (without
having to futz with datasheet column widths). Change the relevant
columns to memo and save the spec, and then the export spec should
work properly, as well.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[oliver james]

David W. Fenton wrote:

I ran onto this problem the other day exporting to tab-delimited
text (for import into a MySQL database on a website). I discovered
something new about saved export specs, that the column types are
there, but hidden, like with hidden columns in datasheets. By
default the export spec datasheet lists only If you put your mouse
cursor over the right-hand edge of the single field name column and
drag, you can open up the other columns (the ones you'd see in an
import spec). Actually, you have to put the mouse over to the right,
a bit away from the edge of the single column header. With fiddling
around like this, you can eventually see the data type column, and
you'll see that it's likely set to TEXT instead of to MEMO.

An esier way of fixing this is to do an export, then start an import
from what you've just exported. Load the saved export spec, and
you'll be able to see the data types more easily there (without
having to futz with datasheet column widths). Change the relevant
columns to memo and save the spec, and then the export spec should
work properly, as well.

I am very keen to understand the suggestion that you outline above,
David, but I don't follow what you mean by an "export spec". Could you
please explain?

Thank you,

Oliver
oliverjames at mailinator dot com

[Danny J. Lesandrini]

In addition to what others have suggested, here are some things
to try:

Remove the DISTINCT keyword from the query (if a query is used)
Remove functions from Memo Field columns in query
{don't use Nz(MyMemo) or IIF() or any functions}
Remove Format property value from Memo field in Query
If it still truncates, remove Format property from all columns

Personally, I can't explain why any of these would help, but they do.
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Taffman" <Da**********@gmail.com> wrote ...

I've searched this goup for an answer to this, there are many
discussions that come close but non that I can find that actually
addresses this particular problem.

I'm exporting queries to Excel. These queries have memo fields in them
Each memo field is trunkated, i.e cuts off after 255 characters.

Is there any way either via VBA or otherwise to get all my memo text to
export to Excel.
I note that there is no such limit when exporting to RTF, but I need
the Excel format.

Any help would be much appreciated, apologies if this has already been
discussed and answered.

[oliver james]

Success!

I removed the functions from the memo field (as suggested by Danny)
AND
I specified the Excel format as 97-2002 (using the File Export menu
choice) (as suggested by Microsoft, referenced by Allen)
THEN
it did not truncate.

(I was not using the DISTINCT keyword or any format properties.)

However, I would still be interested to know more about the Export
specs that Mark referred to.

Cheers,

Oliver

Danny J. Lesandrini wrote:

In addition to what others have suggested, here are some things
to try:

Remove the DISTINCT keyword from the query (if a query is used)
Remove functions from Memo Field columns in query
{don't use Nz(MyMemo) or IIF() or any functions}
Remove Format property value from Memo field in Query
If it still truncates, remove Format property from all columns

Personally, I can't explain why any of these would help, but they do.
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast

[David W. Fenton]

"oliver james" <ol*********@mailinator.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

David W. Fenton wrote:

I ran onto this problem the other day exporting to tab-delimited
text (for import into a MySQL database on a website). I
discovered something new about saved export specs, that the
column types are there, but hidden, like with hidden columns in
datasheets. By default the export spec datasheet lists only If
you put your mouse cursor over the right-hand edge of the single
field name column and drag, you can open up the other columns
(the ones you'd see in an import spec). Actually, you have to put
the mouse over to the right, a bit away from the edge of the
single column header. With fiddling around like this, you can
eventually see the data type column, and you'll see that it's
likely set to TEXT instead of to MEMO.

An esier way of fixing this is to do an export, then start an
import from what you've just exported. Load the saved export
spec, and you'll be able to see the data types more easily there
(without having to futz with datasheet column widths). Change the
relevant columns to memo and save the spec, and then the export
spec should work properly, as well.

I am very keen to understand the suggestion that you outline
above, David, but I don't follow what you mean by an "export
spec". Could you please explain?

Look it up in the Help file.

If you have problems after trying to work it ou on your own, then
post back to the newsgroup.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[oliver james]

David W. Fenton wrote:

I am very keen to understand the suggestion that you outline
above, David, but I don't follow what you mean by an "export
spec". Could you please explain?

Look it up in the Help file.

If you have problems after trying to work it ou on your own, then
post back to the newsgroup.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I had tried to find it in the Help file, but had finally given up.
However your message gave me hope and I did eventually get there! I
never imagined, however, how convoluted it would be to run the Text
export wizard - shouldn't there be a direct option from a menu?!

Cheers,

Oliver

[David W. Fenton]

"oliver james" <ol*********@mailinator.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

David W. Fenton wrote:

> I am very keen to understand the suggestion that you outline
> above, David, but I don't follow what you mean by an "export
> spec". Could you please explain?

Look it up in the Help file.

If you have problems after trying to work it ou on your own, then
post back to the newsgroup.

I had tried to find it in the Help file, but had finally given up.
However your message gave me hope and I did eventually get there!
I never imagined, however, how convoluted it would be to run the
Text export wizard - shouldn't there be a direct option from a
menu?!

It doesn't seem convoluted to me.

And, so far as I can see, there *is* a pretty direct method for
getting there, from the FILE menu.

I'm not sure how much more direct it could be.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/