Stop truncate to 255 Chrs in Excel Export

M

MarcusLesser

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.
 
K

Ken Snell [MVP]

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.
 
J

Jamie Collins

Ken Snell said:
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.
Click to expand...

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.

--
 
J

Jamie Collins

Peter R. Fletcher said:
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
;
Click to expand...

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

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.

--
 
Top