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

Thanks to all who replied!

I am using Office 2010 and I was able to import an Excel worksheet into an existing Access table once.  The data was not correct and I cleared the table, fixed the Worksheet and tried to import again and saw the Subscript out of Range error.  I tried the solutions listed above, and I finally "Compacted and Repaired the Database" and all was well.

I got same problem with access 2010 and got solution for this.

You can do like this:

Step 1. At External Data tab, choose Excel and choose Source of Excel data.

Choose: "Import the source data into new table in the current database"

Remember, excel source data format must be same with table data.

After import done, you save data to new table.

Step 2. Copy new table you've just created and Paste to target table, choose "Append Data to Existing Table".

Now, data from your excel will be added to target table.

Hope it useful.

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

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