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 weird is that the truncation in excel does NOT start at 255 characters, but more downstream so to speak.For example, in the table and the report a long text field record has 15,460 characters.But when exported to excel it contains only the first 13,212 characters and truncates the remaining 2248 characters of the long text field.Please advise! Thank you for your time.Best Regards,Donald

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-bitMS Access for MS 365 MSO (16.01...) 64-bitExcel'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.

03 Access output to Excel truncates data

16 Replies

DonaldCollins
Copper Contributor
May 14, 2021
@Maria Barnes

DonaldCollins

Hi Maria - Hope all is well. Is there any word/update from the internal team ?

Thanks!

Best Regards,
Donald
- MariaBarnes
  MVP
  May 20, 2021
  Not yet, no. Reading between the lines of their answer I would guess 6 months or so to expect a response.
  - DonaldCollins
    Copper Contributor
    May 21, 2021
    Okay thank you for the update !
    
    Best Regards,
    Donald
MariaBarnes
MVP
Mar 01, 2021
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?
- DonaldCollins
  Copper Contributor
  Mar 02, 2021
  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
  - MariaBarnes
    MVP
    Mar 02, 2021
    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?

Forum Discussion

03 Access output to Excel truncates data

16 Replies

Resources