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
    2012-05-04T07:25:51+00:00

    Causes

    1. Field Types don't match

    2.Extra Rows and Columns

    3.Existing Relationships that "enforces data integrity" prevents Access from appending the table due to entires on your records that are not yet present on your other related tables. Temporarily un opt the "enforce" box. Append. Find the new entries, update the other tables, then reinforce the data integrity option again.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-03-30T11:59:57+00:00

    I had this error importing a TXT file into a recently emptied table.

    I compacted and repaired the database and all is well now.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-02-13T18:41:19+00:00

    I've experienced this on many occasions. No amount of editing or re-formatting of columns would fix it. 

    I had a spreadsheet linked to the Access database that I would use for post-processing (pivot tables, charts, macros, etc). The raw import of new data would go smoothly until I started refreshing the data from the linked spreadsheet. Then, the Subscript out of Range error would start upon the next import of new raw data (during the same session). Only by closing both Access and Excel and re-starting them (not just the files, but the whole program) would the error stop. I think there is a connection between a data-link and the import process such that Access can't update a table if it is actively linked to some other source such as a spreadsheet.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-11-13T02:02:08+00:00

    I do not do my *formatting* in Excel, I set up an Import Specification and do it there.  I would try that and see if that fixes your issue oif delete/recreate.  Especially because it is bound to cause something to act hinky somewhere...

    --

    Gina Whipp

    2010 Microsoft MVP (Access)

    Please post all replies to the forum where everyone can benefit.

    Was this answer helpful?

    0 comments