Excel Import field truncation problem (to Access Memo field)

[jhrBanker]

I'm using vba code to import an Excel worksheet (xlsx) to Access 2007. One of the Excel fields contains more than 255 characters, but only the first 255 characters are importing, even though the Access field is a Memo field. Any idea why, or how I can resolve?
Do While xlc1.Value <> ""
rst1.AddNew
rst1.Fields("ProjNbr").Value = xlc1.Offset(0, 0).Value
rst1.Fields("ProjName").Value = xlc1.Offset(0, 1).Value
rst1.Fields("ProjDate").Value = xlc1.Offset(0, 3).Value
rst1.Fields("ProjAmt").Value = xlc1.Offset(0, 4).Value
rst1.Fields("Comments").Value = xlc1.Offset(0, 6).Value
rst1.Update
Set xlc1 = xlc1.Offset(1, 0)
Loop

Please don't question why I'm using vba code or suggest other methods of importing an Excel file. I have my reasons. Just wondering how I can import all the characters from the Excel field into the "Comments" Memo field.



Thanks for your help.

[June7]

Review https://www.accessforums.net/import-...ype-20585.html

[jhrBanker]

None of those situations applies to my problem. The Excel field with the >255 characters is in the first row of data. And the data is being imported to an existing table, with the receiving field type of Memo.

[June7]

It is the same in that VBA code is adding records to a recordset. The thread discussion indicates the issue is not easy to resolve and there is not a 'one fits all' answer. I don't even know if that poster ever solved it.

The best idea that I could derive was to do import with the wizard then use that imported table as the data source for transfer of records to the final table. Of course, your spreadsheet structure might be too complex for the wizard, which is probably why you are using VBA to begin with.

If you want to provide the Excel file, will see what I can do.

[jhrBanker]

There are 14 individuals creating 14 Excel files daily, all in the same format. I am building an Access db for the Admin Asst to use to import these files daily into an Access table. I want to use a form where she can just click a button to import each file automatically. Everything works fine, except that the code is truncating the fields that contains more than 255 characters, even thou the table fields are Memo type. You can use any Excel file for testing.
Thanks

[June7]

I just did a test of VBA importing from Excel a string of 599 characters into recordset. No truncation. I cannot replicate your issue. Provide your file and complete code procedure and I will test.

[jhrBanker]

Problem solved, thou I'm not sure why. It appears that I actually am importing all the characters, but was only seeing the first 255 in the field because the Memo field was formatted as "@", which appears to be the default formatting. When I deleted the @, I can now see all of the characters. Now I need to determine how to change the default formatting. Many thanks for all your help, and sorry it appears that I was leading you down the wrong path.
Thanks again,
JR