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

I have been importing Excel files for years. I recently upgraded to Access 2007 on a shared server (Windows Server 2003 R2, Service Pack 2) and now I can't import. Every time I get "Subscript out of Range" when I use the Import Wizard. The file is correct with the correct field names and data. I have tried importing into different tables and always get this error. I can try an .xls or an .xlsx file and neither work.

I have to paste the records in.

Help!

Sorry I'm a bit late. Just started using Access 2007 and had the same problem importing from Excel 2007. You probably have this figured out already. But for the benefit of others who landed in this site while searching, read on...

I had 14 columns in my table. My first import of about 120,000 rows went without incident. My second import attempt of about 10,000 rows resulted in "Subscript out of range". I noticed that the import wizard sample showed a field named F15 that shouldn't be there. There were only 14 fields (up to F14) in the excel file I was importing, and 14 fields in the table of 120,000 rows from the first import. All I could see in column 15 of the excel sheet was white space. Apparently, someone had deleted information that was there before but for some reason was still being detected by the import wizard. What I did was to select the next ten or so columns in the excel sheet, starting from column 15, clicked on delete entire column, and presto! No more subscript out of range. There are probably other causes and solutions to this problem but this is one of them. I hope this works for you.

I had same issue this morning. Did a manual Compact/Repair and issue resolved.

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.