Results 1 to 14 of 14
  1. #1
    Sephaerius is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    62

    ImportExportSpreadsheet truncates Long Text to 255, please help

    Greetings,
    I have a table that I export into a static Excel workbook using the ImportExportSpreadsheet macro, which does exactly what I need. Despite my table field being set to Long Text, when I run the macro the resulting field in the workbook has been truncated to 255 characters. Is there any simple workaround or solution to this?

    Edit: I'm on Office 2013.


    Thanks!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Sephaerius is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by orange View Post
    You may get some insight from this article by Allen Browne
    Thanks, I've seen that before but I can't quite figure out how to solve the issue. Also, those links are for older versions and I'm on 2013. I'm hoping someone here might have a solution for me to try.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    found this link which discusses a number of possible solutions

    http://datapigtechnologies.com/blog/...-off-in-excel/

  5. #5
    Sephaerius is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by Ajax View Post
    found this link which discusses a number of possible solutions

    http://datapigtechnologies.com/blog/...-off-in-excel/
    Thank you, but that didn't help. The link suggests using the TransferSpreadsheet macro to avoid getting truncated, however he's using an older version of Access. In 2013 the macro is called ImportExportSpreadsheet, and that's the very macro I'm having the truncation issue with.

    I would greatly appreciate any help or suggestions!

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't use macro's - too limiting - so can't help with that but I think I counted 4 different techniques in the link. Are you saying you have tried them all and none of them solve the problem? If so, please explain which ones you tried and what the outcome was, I might be able to determine a variation.

  7. #7
    Sephaerius is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by Ajax View Post
    I don't use macro's - too limiting - so can't help with that but I think I counted 4 different techniques in the link. Are you saying you have tried them all and none of them solve the problem? If so, please explain which ones you tried and what the outcome was, I might be able to determine a variation.
    I apologize if I'm missing something, but that link only provides two methods that I see. Method 1 is to manually export with formatting, which isn't an option for me because I need the contents of my table to export directly into a static Excel workbook. Method 2 was to use the "TransferSpreadsheet" macro, which doesn't exist in 2013 (it's now called ImportExportSpreadsheet, which is what I'm using that's causing the limitation.)

    If you have a VBA-based approach I'm happy to explore that option as well.

  8. #8
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    queries export truncated
    tables do not truncate.

    thanks Microsoft.

  9. #9
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by ranman256 View Post
    queries export truncated
    tables do not truncate.

    thanks Microsoft.
    The data sitting in my table is not truncated, but when I export it to an Excel workbook using ImportExportSpreadsheet, anything over 255 is truncated.

    I'm looking for either a way to fix this problem, or come up with an alternative solution.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    [Exasperated] you need to read the whole thread. You have not provided much information so these may or may not be viable for you

    there may be others but at a glance

    before inserting the contents to the destination excel spreadsheet be sure to format the cells first as “Text” instead of “General”. Then insert your copied contents and you will see all characters (>255) are preserved

    If you are doing Totals and “grouping by” in your query, selecting “First” on the memo field instead of “group by” will allow you to export the full contents of the field.

    Remove the “unique values” property in the query’s design. Apparent reason: Access cannot compare memo fields to one another to determine the uniqueness that you asked for; so it uses the first 256 or so characters to do the match, and in the process the whole field gets cut down to that size when exported.

    I prefer to pull the data in from Excel (database query), which also retains the memo fields

    And Ranman has offered another solution - which rather than just repeating your problem, think about what has been suggested i.e. change your query to a maketable query and export the table.

  11. #11
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by Ajax View Post
    [Exasperated] you need to read the whole thread. You have not provided much information so these may or may not be viable for you

    there may be others but at a glance

    before inserting the contents to the destination excel spreadsheet be sure to format the cells first as “Text” instead of “General”. Then insert your copied contents and you will see all characters (>255) are preserved

    If you are doing Totals and “grouping by” in your query, selecting “First” on the memo field instead of “group by” will allow you to export the full contents of the field.

    Remove the “unique values” property in the query’s design. Apparent reason: Access cannot compare memo fields to one another to determine the uniqueness that you asked for; so it uses the first 256 or so characters to do the match, and in the process the whole field gets cut down to that size when exported.

    I prefer to pull the data in from Excel (database query), which also retains the memo fields

    And Ranman has offered another solution - which rather than just repeating your problem, think about what has been suggested i.e. change your query to a maketable query and export the table.
    I understand your frustration, thank you for trying to help, and I'm not trying to be dense or difficult. I read the entire page you provided and did not discover any tips germane to my issue.

    Please note that I am NOT using query's at any point in THIS particular transfer of data, so suggestions regarding editing a query type does not seem relevant.

    I do not know how much more information I can provide, aside from further spelling out what my issue is:

    1. I have an Access 2013 database which imports, via VBA, records from Excel 2013 workbooks into a Master Table (No issues here, the records import complete into the Master Table.)
    2. A macro called ImportExportSpreadsheet is used to directly export the entire contents of the Master Table into a different, existing, static Excel 2013 workbook. It is at this point that the "Notes" column (containing notes made by staff) is being truncated to 255.

    The original spreadsheet, the master table, and the destination spreadsheet are all formatted correctly to accept Long Text (aka "Memo") greater than 255.

    I've scoured the web for the last week trying to dig up some way to fix this without success. I'm hoping beyond hope that someone might be familiar with the issues I'm dealing with enough to either describe the fix, or let me know for certain that there is no solution. I'm also open to alternative solutions if there's another method I could use to achieve directly exporting the contents of my table into an existing Excel workbook.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The problem is suggestions are made to try and help and the feedback as to their relevance or not is not forthcoming.

    So why go via Access? why not go from excel to the other excel workbook? Do staff enter their comments in Access before exporting?

    And germane to your issue - I would think these are relevant

    before inserting the contents to the destination excel spreadsheet be sure to format the cells first as “Text” instead of “General”. Then insert your copied contents and you will see all characters (>255) are preserved

    I prefer to pull the data in from Excel (database query), which also retains the memo fields

  13. #13
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2013
    Posts
    62
    I opted not to spell out the entirety of my database theory and design in an effort to be concise regarding my specific issue and respectful of both my time and yours. I apologize for the breakdown in communication, I've provided all the information I have and have given feedback on the links that were given. I'm going to head over to another forum to see if anyone else may have any knowledge regarding my issue. Thank you for attempting to help me.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    No problem - please put a link back to this thread so other responders don't waste their time suggesting the same thing

Please reply to this thread with any new information or opinions.

Similar Threads

  1. long text question - I think ;-)
    By Jen0dorf in forum Access
    Replies: 2
    Last Post: 01-15-2016, 10:11 AM
  2. Unbound text box truncates leading zeros
    By brharrii in forum Forms
    Replies: 4
    Last Post: 01-10-2016, 08:06 PM
  3. Long Text Box
    By data808 in forum Access
    Replies: 5
    Last Post: 09-24-2014, 12:26 AM
  4. Is it possible to make 'long text' longer?
    By rgrstvr in forum Access
    Replies: 1
    Last Post: 07-04-2014, 06:37 PM
  5. Text too long, error message
    By taylormotm in forum Access
    Replies: 13
    Last Post: 06-18-2009, 07:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums