DoCmd.TransferSpreadsheet acImport - Long Text Import Problem

Ivan Howard

Active Member
Joined
Nov 10, 2004
Messages
332
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.

VBA Code:
DoCmd.TransferSpreadsheet acImport, 10, strTableToRefresh, strSourceFileFullPath, True, strSourceFileImportRange

Any ideas as to what I might be doing wrong?

Thanks very much in advance.
Ivan
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,306
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Yeah, trying to import long text fields is a pain!
 

Ivan Howard

Active Member
Joined
Nov 10, 2004
Messages
332
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!
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,131
Messages
5,835,579
Members
430,368
Latest member
User800

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top