locked
Access Long Text not Import Correctly from Excel Spreadsheet RRS feed

  • Question

  • I have tried to import Excel data into Access table. There are 6 columns in Excel data Sheet. Only columns which contain data less than 255 characters are imported. The rest of the columns were not imported because they contain the combination of data between 90 to 3000 characters (not a single piece data was imported). 

    I done both built the table with "long text", then use Excel VBA import and just using wizard to import. The two methods produced exactly the same result. Using "long text" should have work, but it is not.

    Any idea, how to get around this problem?

    Thanks

    My code in Excel:

        Dim acc As New Access.Application
        acc.OpenCurrentDatabase "C:\Users\ssangcha\Documents\CR Review\CR HSL-C0129\HLS-RQMT-0001.accdb"

        'acc.Visible = True
        
        
        
        acc.DoCmd.TransferSpreadsheet _
                TransferType:=acImport, _
                SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
                TableName:="HSL-RQMT-001-Rev-D", _
                Filename:=Application.ActiveWorkbook.FullName, _
                HasFieldNames:=True
                'HasFieldNames:=True, _
                'Range:="Rev_C$A3:H731"
        acc.CloseCurrentDatabase
        acc.DoCmd.TransferSpreadsheet
        acc.Quit
        Set acc = Nothing

    Tuesday, November 17, 2020 1:12 AM

All replies

  • Joe,

    "I have tried to import Excel data into Access table."

    You want to "Export" from Excel to Access? Then it should be "Export".

    Tuesday, November 17, 2020 6:22 AM
  • Alternatively IMPORT the Excel data from Access as that wil give you more control over the data.

    In particular, ensure there is no formatting applied to the imported data to ensure it is not truncated at 255 characters

    Tuesday, November 17, 2020 9:29 PM