Stop truncate to 255 Chrs in Excel Export

MarcusLesser

unread,

Nov 17, 2004, 6:29:04 AM11/17/04

to

I have a query which I regularly export to Excel. A couple of fields are set
as memo in Access and sometimes have more than 255 characters. Is there
anyway I can stop these being truncated to 255 characters when I export. I
should point out I'm just using the standard 'analyse it with MS Excel'
function.

Ken Snell [MVP]

unread,

Nov 17, 2004, 8:32:58 AM11/17/04

to

If you use the manual export (FIle | Export) process, this truncation occurs
because that process outputs the data using Excel 95 format, which did not
support character strings over 255 characters long.

Instead, use a macro or VBA code to do the export, using the
TransferSpreadsheet method. See Help for info on how to set this up.

--

Ken Snell
<MS ACCESS MVP>

"MarcusLesser" <Marcus...@discussions.microsoft.com> wrote in message
news:F0BCBB51-D01D-46BC...@microsoft.com...

Peter R. Fletcher at hyphen

unread,

Nov 17, 2004, 2:40:08 PM11/17/04

to

You have to explicitly tell it to use the right (latest) Excel format
- Excel9, from memory. The default is the same older format as that
used in manual exports.

On Wed, 17 Nov 2004 08:32:58 -0500, "Ken Snell [MVP]"
<kthsne...@ncoomcastt.renaetl> wrote:

>If you use the manual export (FIle | Export) process, this truncation occurs
>because that process outputs the data using Excel 95 format, which did not
>support character strings over 255 characters long.
>
>Instead, use a macro or VBA code to do the export, using the
>TransferSpreadsheet method. See Help for info on how to set this up.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

Jamie Collins

unread,

Nov 18, 2004, 7:35:29 AM11/18/04

to

"Ken Snell [MVP]" <kthsne...@ncoomcastt.renaetl> wrote in ...

> If you use the manual export (FIle | Export) process, this truncation occurs
> because that process outputs the data using Excel 95 format, which did not
> support character strings over 255 characters long.
>
> Instead, use a macro or VBA code to do the export, using the
> TransferSpreadsheet method.

Another option is to specify Excel 8.0 in a query e.g.

SELECT MyMemoCol
INTO [Excel 8.0;Database=C:\OutFile.xls;].NewExcelTable
FROM MyTable
;

Jamie.

--

Peter R. Fletcher at hyphen

unread,

Nov 19, 2004, 5:55:13 AM11/19/04

to

On 18 Nov 2004 04:35:29 -0800, jamiec...@xsmail.com (Jamie Collins)
wrote:

I've never run into that approach, which is extremely cute - where is
it documented?

Jamie Collins

unread,

Nov 22, 2004, 3:23:04 AM11/22/04

to

Peter R. Fletcher <pfletch(at)fletchers(hyphen)uk.com> wrote in ...

> >Another option is to specify Excel 8.0 in a query e.g.
> >
> > SELECT MyMemoCol
> > INTO [Excel 8.0;Database=C:\OutFile.xls;].NewExcelTable
> > FROM MyTable
> > ;
>

> I've never run into that approach, which is extremely cute - where is
> it documented?

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q295646
How To Transfer Data from ADO Data Source to Excel with ADO

As with many MSDN articles, you have to look beyond the title i.e. the
syntax is odbc pass-through and does not use OLE DB on which ADO is
based.

Jamie.

--