none
Linking to Excel / Importing From Excel Issue RRS feed

  • Question

  • I trying to import (or link) my access db to an excel workbook.  My issue is with respect to a text fields.  The excel file has cells with lengthy text (>300 characters).  My problem is when I link or import, that cell content is truncated to 255 characters.  What am I doing wrong?  How do I fix this?

    My link command is:

    DoCmd.TransferSpreadsheet acLink, , "tblExcelResults", "C/WTS/Results.xlsx", True, "Results!"

    The workbook has only one worksheet, named 'Results".

    TIA,
    -Pat

    Wednesday, September 19, 2018 9:08 PM

Answers

  • Thanks for your feedback.  Without changing my command line I was able to make it work. 

    Here are my 'assumptions' how Ms Access defines fields when import (or linking) to Excel with the table having a header title row.

    When Ms Access starting reading the Excel table, the first, non-header, row cell definition and content defines the resulting field definition.  It's a combination of both the excel cell format and the content of the cell. Let me give you an example.  I have a 'comments' field (Excel column). With the first, non-header, cell is formatted 'text' and  over 255 characters text, the field is truncated in access.  If the cell is formatted 'general', all the characters are visible in access.  It's a matter of the definition and content of the first, non-header, cell.

    I just inserted into the first row of my Excel table with proper cell formatting and content (string longer than 255 characters).

    Regards,
    -Pat 

    • Marked as answer by PSD1953 Friday, September 21, 2018 2:47 PM
    Friday, September 21, 2018 2:47 PM

All replies

  • You're not doing anything wrong, ACCESS fields are limited to 255 text characters.

    Wednesday, September 19, 2018 10:27 PM
  • When I test with an Excel workbook with cells that have more that 255 chars it works just fine.  That said, there are a couple errors in your code, missing : in your path, ! is an illegal character for a Range.  When I then open the table and check, the entire content is there.

    What version of Access are you working with?

    Now, when you run queries on tables with Memo/Long Text, it is true that Access will truncate them at 255 chars, but there are work aorunds, see http://allenbrowne.com/ser-63.html

    Also, depending on what you are trying to accomplish, you might turn towards Excel automation as a solution.


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, September 20, 2018 8:49 AM
  • Thanks for your feedback.  Without changing my command line I was able to make it work. 

    Here are my 'assumptions' how Ms Access defines fields when import (or linking) to Excel with the table having a header title row.

    When Ms Access starting reading the Excel table, the first, non-header, row cell definition and content defines the resulting field definition.  It's a combination of both the excel cell format and the content of the cell. Let me give you an example.  I have a 'comments' field (Excel column). With the first, non-header, cell is formatted 'text' and  over 255 characters text, the field is truncated in access.  If the cell is formatted 'general', all the characters are visible in access.  It's a matter of the definition and content of the first, non-header, cell.

    I just inserted into the first row of my Excel table with proper cell formatting and content (string longer than 255 characters).

    Regards,
    -Pat 

    • Marked as answer by PSD1953 Friday, September 21, 2018 2:47 PM
    Friday, September 21, 2018 2:47 PM