Subscript Out of Range error when importing into Access 2007 from Excel.

I had this problem. In my case, one of the problems was that I included the ID numbers in the Excel Spreadsheet, while, on Access the ID numbers was set to be automatic.

Then, I deleted some columns to the left of the table and some rows from below, 

Also, on the excel sheet, I did not put on the column headers - I put on only the data. Once, I added the column headers to the excel spreadsheet, by the grace of god, it worked.

Gina,

Thanks for the very helpful info.

I have checked the db structure, and the AutuNumber field is set to Long and Increment.

Here is where I am:

1. Back up your database. - Yep
2. Create a new module. In Access 2007 and later, click Module (rightmost icon) on the Create ribbon. Yes
3. Copy the function below, and paste into the code window. Yes
4. Choose References from the Tools menu, and check the box beside: "Microsoft ADO Ext. 2.x for DDL and Security". Yes, although there are other boxes checked: Visual Basic; Microsoft Office 14.0 Object Library; OLE Automation; Microsoft Office 14.0 Access database Engine Object Library, in addition to the above box.
5. From the Debug menu, choose Compile to check there are no problems.  When I do this, Compile is Grayed out - It won't let me uncheck any of the above other boxes.  Step 6 doesn't seem to do anything.
6. Press Ctrl+G to open the Immediate Window. Enter:

    ? AutoNumFix()

Perhaps other steps are needed after step 3?

Thanks, again, Chris

I created a module, and pasted in the code referenced. When I open the Immediate window and enter "? AutoNumFix()" all that happens is I get a zero on the next line.  If I try it without the ?, I get "Compile Error"

When the instruction says to do Debug/Compile, the Compile link is grayed out.

Chris,

Let's deal with the Key Violations first... this indicates that Autonumber has become *corrupt*, in your case trying to insert numbers that are already in use.  I know you are not importing that field but this does happen all by itself because of other issues, see...

http://allenbrowne.com/ser-40.html

I know that this is an old post, but I've been importing Excel sheets into Access for years, and now I'm getting errors. Different errors, sometimes the "Subscript out of range" other times, simply a long message that 0 rows were deleted; 0 rows were edited, with zero details on why.  Pretty disappointing!

Here are my answers to Gina's questions:

1. How many columns is the spreadsheet?     26
2. Do you have any calculated columns?         NO
3. Have you looked at the spreadsheet to confirm there are no errors on the

sheet?  NO - I've tried saving as a CSV and as an XLXS, with the same results.

4. Just on a whim here, do you column headings use any Reserved Words (or

Wildcard Characters) that might confuse Access?  No wildcards, if fact, I tried EXPORTING to Excel, then removing the exported data but keeping the column names, and pasting in the new data - still get an error.

http://allenbrowne.com/AppIssueBadWord.html

5. Have you tried it manually? (You know, File... Import...)  I am only doing this manually; i.e., not via saved import steps.

I've tried grabbing the whole spreadsheet and Unhiding Columns

I've tried manually formatting each and every column to the correct data type.

My most frequent error says: "The contents of fields in 0 record(s) were deleted and 10 record(s) were lost due to key violations.

I only have one Primary key, which is an autonumber field in Access.  I am not including that column in the Excel sheet, but I never have in the past, without any errors.

Help!

Thanks,

Chris