doCmd OutputTo excel (trims varchars)

[Brian1960]

HI,

I have code which exports a table to a spreadsheet. It works fine apart from a Varchar 5000 field (from a SQL server) which is only shows the first 255 characters, when it opens the Excel Spreadsheet.
I have checked the orginal table (which is created on the fly) and it has all the data.
This is the code used to export the data.

DoCmd.OutputTo acOutputTable, sObjectname

acOutputTable = 0
and
sObjectname = The table being exported.

Thanks
Brian

 

[dbDamo]

Excel limitations I'm afraid, it only allows 255 characters per cell. No way around this so you shouldn't export this field to Excel, not sure why you would want to anyway?

 

[Brian1960]

If you open the table and Analyse with Excel, then it shows all the text.
Excel is not limited to 255 char per cell. Test it to see.

 

[dbDamo]

My apologies I should have been clearer. I know that the character limit for cells in Excel is no longer 255 and hasn't been for quite some time (since 97 I believe) but when exporting from Access, the 255 character limit in Excel is still used - very strange I know!!

There a few ways that people use to get around this:-

1. If using 2007, export as a .xlsx file
2. Export as a .csv file
3. Use TransferSpreadsheet instead of OutputTo (apparently some people have had success doing this, though I'm not so sure)
4. Split your memo field into multiple 'sub-fields' with a character limit of 255. When the data has been exported to Excel, use a simple formula to concatenate the fields

There are probably more ways around it, but these seem to be the most commonly used workarounds.