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
    2013-12-26T04:27:47+00:00

    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.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-09-04T20:26:18+00:00

    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.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-09-03T21:44:33+00:00

    I had the same error message.  I closed Access.  After reopening and attempting to import again I encountered no errors.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-07-30T20:54:18+00:00

    Remove all visual formatting ...

    Was this answer helpful?

    0 comments