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]

Re: Memo Text Truncation during Excel Export

Does this article help:
Memo Field Truncated When Report Is Output to Excel
at:

http://support.microsoft.com/default.aspx?scid=kb;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" <David.Toombs@g mail.com> wrote in message
news:1133522403 .175094.191280@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> 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.
>[/color]

[David W. Fenton]

Re: Memo Text Truncation during Excel Export

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in
news:439065de$0 $8552$5a62ac22@ per-qv1-newsreader-01.iinet.net.au :
[color=blue]
> 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?[/color]

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]

Re: Memo Text Truncation during Excel Export


David W. Fenton wrote:[color=blue]
> 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.
>[/color]

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]

Re: Memo Text Truncation during Excel Export

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
dlesandrini@hot mail.com

http://amazecreations.com/datafast

"Taffman" <David.Toombs@g mail.com> wrote ...[color=blue]
> 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.
>[/color]

[oliver james]

Re: Memo Text Truncation during Excel Export

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:[color=blue]
> 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
> dlesandrini@hot mail.com
> http://amazecreations.com/datafast[/color]

[David W. Fenton]

Re: Memo Text Truncation during Excel Export

"oliver james" <oliverjames@ma ilinator.com> wrote in
news:1134563203 .520672.283420@ g47g2000cwa.goo glegroups.com:
[color=blue]
> David W. Fenton wrote:[color=green]
>> 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.
>>[/color]
>
> 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?[/color]

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]

Re: Memo Text Truncation during Excel Export


David W. Fenton wrote:
[color=blue][color=green]
> >
> > 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?[/color]
>
> 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/[/color]

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]

Re: Memo Text Truncation during Excel Export

"oliver james" <oliverjames@ma ilinator.com> wrote in
news:1135038332 .555380.233390@ g14g2000cwa.goo glegroups.com:
[color=blue]
> David W. Fenton wrote:
>[color=green][color=darkred]
>> > 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?[/color]
>>
>> 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.[/color]
>
> 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?![/color]

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/