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...
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.Hi Maria,
Thank you for you help - there was a bit of back and forth last week internally within the regulatory group regarding sending you a db with the example of the issue ie, " If you can give me a database that shows what you are seeing, and information about version/build of Office, I can look further. ".
I am not sure how they want to proceed at this time.
I am suggestioning a bare-bones db with just the problem record and output as a test case.
I hope to get back with something mid-week.
Thanks!
Best Regards,Donald