Stop truncate to 255 Chrs in Excel Export

[Guest]

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]]

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.

 

[Peter R. Fletcher]

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.

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]

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]

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.

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

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

 

[Jamie Collins]

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.

--