how to export query to Excel without truncating Memos to 255 chars?

Question

Using Access 2010. I have a query with several Memo fields in it that I would like to export to an Excel sheet. Every method I have tried so far has truncated the Memo fields:

• Export Wizard
• Right-clicking on the query and choosing Export
• Exporting to CSV
• DoCmd.TransferSpreadsheet
• Copy/pasting the data into open Excel sheet

How can I get all the Memo data in my export, or at least the full 32767 characters that an Excel cell can display?

Typescript

datapigtechnologies.com/blog/index.php/… (Have you tried all of the methods on that page?) – Overcareful

See Truncation of Memo fields. – Maki

Answer 1

Adaam's suggestion was very helpful. I ended up doing this the following way:

1. Select query into table.
2. In design view, change relevant Text fields to Memo fields, because the SELECT INTO automatically set them as Text.
3. Delete all rows from table.
4. Add rows from query using INSERT INTO.
5. Import table contents into Excel range using Range.CopyFromRecordset. I had to do this from the table instead of directly from the query because I had UDFs in my query.

Typescript

Answer 2

Export into excel with option "Export data with formating and layout" set to YES. If this checkbox is not set to yes, access truncate texts to first 255 characters.

enter image description here

Tantivy

Thank you, I was having the same issue and this was the solution. – Shrike

Answer 3

We are using older version of MS Access and XL and cannot upgrade so "export data with formatting and layout" is not available. Doing Copy > Paste Special > Text worked for our use case and didn't have to rework queries etc. but doesn't work if answer set is more than a few thousand rows.

Marilynnmarimba