how to export query to Excel without truncating Memos to 255 chars?
Solution 1
Adaam's suggestion was very helpful. I ended up doing this the following way:
- Select query into table.
- In design view, change relevant Text fields to Memo fields, because the
SELECT INTO
automatically set them as Text. - Delete all rows from table.
- Add rows from query using
INSERT INTO
. - 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.
Solution 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.
Solution 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.
sigil
Updated on June 18, 2022Comments
-
sigil almost 2 years
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?
-
Robert Patrician about 5 yearsThank you, I was having the same issue and this was the solution.