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

Posted on 2008-11-06
Last Modified: 2013-11-27
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

Question by:jpirozzolo
    LVL 59

    Accepted Solution

    Its because....;en-us;Q281517

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

    Assisted Solution

    by:Rey Obrero
    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
    LVL 3

    Expert Comment

    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

    Author Comment

    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!

    Author Comment

    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.  :)



    Author Closing Comment

    Thanks for your help folks!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now