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-03-17T13:21:41+00:00

    Here is what worked for me when everything appeared correct (and it was) but I was getting the Subscript Out of Range error message:

    I perform Compact & Repair (under File tab in Access 2007-2010) and afterwards my Excel import worked like a charm. 

    Good luck.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-11-13T10:04:33+00:00

    Dear all,

    I was experience the same issue, both from txt and Excel - file.

    The course to me issue was due to the fact that Access 2010 couldn't read the text / field delimiter.

    After I changed the the delimiter from asterix 124 to ; it all worked fine.

    http://www.asciitable.com/

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-11-08T15:33:15+00:00

    This is the most concise answer I've found to stop the 'subscript out of range error'

    It isn't a 'work around', it's a FIX

    http://www.judiwa.com/2010/09/13/access-2010-subscript-out-of-range-at-excel-data-import/

    Was this answer helpful?

    0 comments
  4. Anonymous
    2012-11-05T15:00:02+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.

    I tried all of the other suggestions and none of them worked until this one!  It is a little annoying to have to close and reopen the Access database between every data upload, but it definately beats any alternative i've tried.  Thanks!

    Was this answer helpful?

    0 comments