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
    2010-06-16T21:53:29+00:00

    Hi,

    Thanks for your reply. Here's the answers to your questions:

    1. How many columns is the spreadsheet? - 23

    2. Do you have any calculated columns? - No

    3. Have you looked at the spreadsheet to confirm there are no errors on the

    sheet? Since this has happened on several different sheets, I'm assuming it's not particular to the sheet itself. What kind of erros do you mean?

    4. Just on a whim here, do you column headings use any Reserved Words (or

    Wildcard Characters) that might confuse Access?

    http://allenbrowne.com/AppIssueBadWord.html

    Here are my column headings.

    5. Have you tried it manually? (You know, File... Import...)

    I don't know how to do this in Access 7. I go to the External Data tab and choose import. I'm importing it into an existing table. I'm doing what I have always done in Access 3 and now it doesn't work. Did something change? I appreciate your help.

    Thanks!****

    Was this answer helpful?

    20+ people found this answer helpful.
    0 comments
  2. Anonymous
    2010-06-16T05:00:37+00:00

    ccento,

    Several reasons for this... Could be the Excel spreadsheet has too many

    columns, could be there is some corruption in the Excel spreadsheet, could

    be the way Access is translating your columns in Excel, could be there are

    calculated fields in Excel that Access can't translate for what ever reason.

    So going to need a wee bit more information...

    1. How many columns is the spreadsheet?
    2. Do you have any calculated columns?
    3. Have you looked at the spreadsheet to confirm there are no errors on the

    sheet?

    1. Just on a whim here, do you column headings use any Reserved Words (or

    Wildcard Characters) that might confuse Access?

    http://allenbrowne.com/AppIssueBadWord.html

    1. Have you tried it manually? (You know, File... Import...)

    --

    Gina Whipp

    2010 Microsoft MVP (Access)

    "ccento" wrote in message news:2959259c-5035-4838-b8cd-29a22e19e567...

    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!

    -- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments