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

Anonymous
2010-06-16T03:47:16+00:00

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!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2010-09-03T16:30:08+00:00
  1. Just on a whim here, do you column headings use any Reserved Words (or

Wildcard Characters) that might confuse Access?

This was my problem. I had Serial# and # a few other places!

Was this answer helpful?

300+ people found this answer helpful.
0 comments

50 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2017-05-01T02:04:42+00:00

    I was getting the same error for a while too. It was very annoying.

    It came down to my column names and making sure the columns were formatted for the correct data in Excel before importing it into Access. Once I made sure the column names were identical (including copying and pasting the formatting from/in Excel), I didn't get the name errors. And once I made sure the formatting (date, text, yes/no boxes) was correct, the "Subscript out of Range" error disappeared and I was able to save the import process so I don't have to do it all over again if the source file gets updated.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-04-04T17:34:37+00:00

    Had the same thing. Did Save As and chose older version Excel and that file imported fine!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-03-09T16:39:01+00:00

    I sometimes experience this problem in Access 2007, too. Sometimes I am not able to import from Excel 2007 into an existing table. The existing table was created by a prior import process. I can import into a new table without any problem. However, if I have to do that every time, then that reduces the efficiency of the application. It seems like the table in Access somehow becomes corrupted, but I'm not sure why. It would be nice if Microsoft would make the error reporting more robust. Stating "subscript out of range" is like calling 911 and stating "there is an accident somewhere in the city".

    For anyone else who made need help, my issue was that my original table in access that I was appending to was also imported from Excel to Access and Access did not make my primary key column an Autonumber data type. So I had to:

    • go to my original table in Access, and in Design view create another field called NodeID (NodeID was already the primary key in my table when I imported from Excel but Access didn't make it Autonumber data type--Access made it just a Number data type).
    • made it so the old NodeID field was no longer the primary key field.
    • set new NodeID field to Autonumber data type, and then made it a primary key.
    • I then moved the two NodeID fields together and viewed the table in datasheet view to make sure the autonumbers matched up with the NodeID field that I originally had.
    • I deleted the old NodeID field and was left with the new NodeID primary key field that is now an Autonumber data type.

    Then I was able to append to this table from a table in Excel. When I import tables into Excel, I now always open the table in Design view and check that Access matched the data types correctly.

    Besides the primary key ID field getting changed to just a Number data type vs. an Autonumber, sometimes Comment fields will be set to Short Text and I need them as Long Text (or Memo in the older versions of Access), for example, and Access might make other changes, too.

    P.S. As others mention, I also made sure the column headings matched between Excel and Access exactly and that the columns in Excel are formatted to match format of the columns in Access, so date fields need to be formatted as date fields in Excel, numbers fields (especially primary or foreign key fields--the ID fields) need to be formatted as numbers in Excel.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-02-21T12:51:18+00:00

    I had the same problem. I restarted Access and I was able to import successful

    Was this answer helpful?

    0 comments