• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1364
  • Last Modified:

Exporting Access data to Excel seems to truncate text to 255 characters

Hi Experts,

I wonder if anyone can help with an exporting problem from MS Access 2003 to Excel 2003.
Basically the attached code creates an Excel spreadsheet and launches a template file (Word Template) that opens the exported spreadsheet, and runs a mail merge etc.  The export item 'Qry_MM_Export' is a query.

The process works, however there is one column that exceeds 255 characters, and when it exports it to the spreadsheet, it seems to truncate the data.  But when you look at the query itself, the data is all there.

Please could someone help me export all the data to the spreadsheet without it truncating.

(By the way, the truncated column is not a field from a table, it is text that comes in from a function)

Thanks VERY much in advance
Jpirozzolo.


'//Now export the data and shell the file.
            ExportFile = "C:\MailMergeExport.xls"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Qry_MM_Export", ExportFile, True
a = ShellApp(TemplateFile, vbMaximizedFocus)

Open in new window

0
jpirozzolo
Asked:
jpirozzolo
2 Solutions
 
Saurabh Singh TeotiaCommented:
Its because....

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q281517

you can do the necessary changes and try as mentioned in the link...
0
 
Rey Obrero (Capricorn1)Commented:
is the query based on one table or more?
if one  table, just use the table for export instead of the query..

also see this link, why
Truncation of Memo fields
http://allenbrowne.com/ser-63.html
0
 
garyb2008Commented:
Hi, if none of the above solutions work you could test using TransferText instead of TransferSpreadsheet and see if the data in the text file is truncated in the same fashion.

a quick search in Access help will reveal how to use TransferText

Hope this helps
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jpirozzoloAuthor Commented:
Hi folks, Thanks for your replies.

The query is based on 1 table, but it uses expressions to collect information and work out data from other records in the same table.  It is the expression that is truncated to 255, and according to the link from capricorn1 (thank capricorn1), Access treats such expressions as 255 text fields.  (I have the same issue with transfertext)

Apparently I need to do something with the query to append the results to a table or something like that.  Not done that before, so will need to play around with it.
Any pointers will be really appreciated... Thanks!
0
 
jpirozzoloAuthor Commented:
Hi.
Thanks for your help in pointing me in the right direction.  I have managed to resolve this issue now :)
Basically I decided to do away with the expression and create a memo field in the record that the query uses.  Then before running the query, I created a private sub that collected the data that I wanted (the stuff that is over 255 chars and was originally an expression) and then using SQL, send it to the new memo field.  The query then just opens up the record data with the new memo field.  Then after exporting the data to excel, all info (after 255 chars) seems to be there.  :)

Regards,

Joe.
0
 
jpirozzoloAuthor Commented:
Thanks for your help folks!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now