Forum Discussion
03 Access output to Excel truncates data
Hi Community, We have an Access "Long text" data type field. In the Access report, all the data in the field is displayed. When the data is exported to excel, the field is truncated. What is weir...
You do not say what version of Excel you are using. Excel actually has quite a long limit more like 32,000 to the definition of what can be stored in a cell, however what actually displays in a cell can be less that that for older versions (2003 and older). In more current versions it should be the same. Can you copy and paste the value manually from Access to the Excel result and then see the whole result?
Hi Maria,
Thank you for your help.
Sorry - you're right I should have included that - they're both 365...
MS Excel for MS 365 MSO (16.01....) 64-bit
MS Access for MS 365 MSO (16.01...) 64-bit
Excel's 32k cell limiit - that's the other oddity as you found too, the cell has plenty of room to hold the data coming from Access.
Yes, we can copy and paste manually form Access to Excel, no problems.
But when this Access form button exports table data to excel , it has truncation.
Perhaps that is where the issue is ? At the form/macro./vb layer?
Best Regards, Donald
Thank you for your help.
Sorry - you're right I should have included that - they're both 365...
MS Excel for MS 365 MSO (16.01....) 64-bit
MS Access for MS 365 MSO (16.01...) 64-bit
Excel's 32k cell limiit - that's the other oddity as you found too, the cell has plenty of room to hold the data coming from Access.
Yes, we can copy and paste manually form Access to Excel, no problems.
But when this Access form button exports table data to excel , it has truncation.
Perhaps that is where the issue is ? At the form/macro./vb layer?
Best Regards, Donald
- Thanks Donald. Can you explain exactly how you are exporting to Excel? Are you opening the report in Print Preview mode and then using the Excel export on the Data section of the ribbon?
- Hi Maria,
I asked the developer and it seems that is how is exported.
This is her reply;
"
Yes, the button on the report forms opens print preview and then click on excel from there. There’s probably a “prettier” way to do this but since I don’t have that knowledge, I went with what worked.
"
Best Regards,
Donald- I talked to one of the Access Team and this is what he said:
When exporting to older Excel formats (Excel 97-Excel 2003 or older), we export a maximum of 255 characters.
When exporting to current Excel formats, we export more than 13000 characters.
When using the Excel button from Report Print Preview, we only give you the option to export to the older formats (because the code for exporting reports was never updated for the newer Excel formats, only the code for exporting tables)
So, I’m not sure how you get the case where we are exporting >255, but <15000 characters.
If you can give me a database that shows what you are seeing, and information about version/build of Office, I can look further.
If not then you may be able to get around this by exporting directly from the table data instead.