Stop truncate to 255 Chrs in Excel Export

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
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.

--
 
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.
Click to expand...

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

--
 
Back
Top