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. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2012-07-30T16:09:20+00:00

    I have this problem with a specific set of circumstances that may prove instructive:

    I upload a series of different excel 2007 sheets into their respective tables in access 2007.

    When I load sheets of the same # columns consecutively, they load fine. When I load sheets of varying columns the 1st sheet loads the next sheet with a varying column amount bugs out with the subscript error. Normally I simply exit and re-load access and continue loading sheets until it bugs again.

    (loading = importing below)

    HOWEVER today I tried something different. I loaded the first sheet per normal and tried sheet #2 with different columns. Bugged out. Re-loaded access.

    I then loaded sheet #2. Rather than load sheet #3, I ran other functions I normally use in access. THEN loaded sheet #3 (which ALWAYS bugs out after sheet #2).

    Didn't bug out.

    There is some reason you can't load consecutive sheets with differing column numbers even if going to different tables with the right respective columns for each.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-06-27T02:44:17+00:00

    DavidcWhite,

    I don't use that in Excel so no I never had that problem.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2012-06-26T16:23:53+00:00

    Same frustrating problem.

    My solution - copy the problem page to word and save as a txt file .

    load to ms access as a txt file, it will assume tab delimited for columns.

    seems to overcome the problem.

    also - do you know that the excel copy and past down option  doesnt work while the internet explorer is on?

    Was this answer helpful?

    0 comments