DoCmd.TransferSpreadsheet acImport - Long Text Import Problem

[Ivan Howard]

Hello all,

Please could someone kindly give me a hand. I have been looking for a solution for 2 days and keep turning up empty handed.

I am using the following VBA code to import data from a predefined spreadsheet containing 84 columns into an existing Access Table. The macro imports 83 of the columns 100% accurately and I don't get any run-time errors. The issue that I have is that the remaining column is a "Comments" field that contains some records with more than 255 characters. These records gets truncated every time I run the process.

The "Comments" column in the spreadsheet has been run through Text To Columns as "Text" and the cell formatting has also been set to "Text".
The "Comments" field in the Access Table is set to "Long Text" and it does not contain any formatting parameters (@) at all.

I have removed the "Comments" field, saved the Table, Compact and Repaired the database and recreated the field and it still won't import the dataset without truncating the "Comments" field.

DoCmd.TransferSpreadsheet acImport, 10, strTableToRefresh, strSourceFileFullPath, True, strSourceFileImportRange

Any ideas as to what I might be doing wrong?

Thanks very much in advance.
Ivan

 

[Joe4]

I think you need to change that particular field to "Rich Text", as explained here:

https://answers.microsoft.com/en-us/msoffice/forum/all/long-text-limit-254/7b927011-769f-416d-b0f7-352770270808?page=2

 

[Ivan Howard]

I think you need to change that particular field to "Rich Text", as explained here:

https://answers.microsoft.com/en-us/msoffice/forum/all/long-text-limit-254/7b927011-769f-416d-b0f7-352770270808?page=2

Thanks very much Joe. I will take a look at it. I appreciate your help.

 

[Joe4]

You are welcome.
Yeah, trying to import long text fields is a pain!

 

[Ivan Howard]

You are welcome.
Yeah, trying to import long text fields is a pain!

I had a look at the link you sent through - thank you.

I tried the "Rich Text" setting and it didn't work.

However, on the same thread you sent, I then saw something that said something about the first couple of records imported, determine some field settings in the Access Table - which I find completely ridiculous... but I indulged... Thankfully the pre-defined dataset that I import into Access, is created through an Excel macro that I created and this macro essentially cleans, re-formats, etc. the data before it is imported into Access. I edited the Excel macro to do the following:

1. Insert a column after the "Comments" column.
2. In the new column, I calculated the length of the comment in the adjacent cell and filled the range.
3. Copy and pasted the calculated lengths as values.
4. Sorted the lengths in descending order.
5. Deleted the inserted column.
6. Saved and Closed.

I then imported the file and it works 100%.

Why oh why!

Thank you for pointing me in the right direction Joe. I can now put the last 2 days to bed!