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

'//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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Its because....


you can do the necessary changes and try as mentioned in the link...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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!
jpirozzoloAuthor Commented:
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.  :)


jpirozzoloAuthor Commented:
Thanks for your help folks!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.