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-08-26T02:24:28+00:00

    Is there a question in there somewhere that I missed?  Did you review the above?  Does any of apply to you?

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

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2010-08-25T22:40:34+00:00

    I sometimes experience this problem in Access 2007, too. Sometimes I am not able to import from Excel 2007 into an existing table. The existing table was created by a prior import process. I can import into a new table without any problem. However, if I have to do that every time, then that reduces the efficiency of the application. It seems like the table in Access somehow becomes corrupted, but I'm not sure why. It would be nice if Microsoft would make the error reporting more robust. Stating "subscript out of range" is like calling 911 and stating "there is an accident somewhere in the city".

    Was this answer helpful?

    100+ people found this answer helpful.
    0 comments
  3. Anonymous
    2010-06-17T01:36:28+00:00

    Since you are using 2007 the command to import an Excel file is:

    1. External Data tab, Imort group, Excel,
    2. Use the Browse button to locate and select your file
    3. Leave the first option button on "Import the source data into a new table..."
    4. Click OK.
    5. Choose the worksheet or range name and click Next.
    6. Check the First Row Contains Column Headings button if appropriate

    7.  Click Next (probably nothing to do here)

    8.  Click Next (choose the primary key options that you want)

    9.  Click Finish.  ..

    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2010-06-16T22:57:54+00:00

    Thank you for the answers... however, could you please go to File-External

    Data and attempt to import your file manually and report the results here.

    Oh, bring it into a new table so as not to mess up your regualr table.

    --

    Gina Whipp

    2010 Microsoft MVP (Access)

    "ccento" wrote in message news:3a790919-f617-49f2-8490-566b759ae4ec...

    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?

    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

    Here are my column headings.

    MemberID FirstName LastName Address Address1 City State PostalCode

    Country HomePhone International Phone EmailAddress EMIMemberDate TA

    Grid/Referral Staff EPI CD PC MAC Potential Sponsor Europe Contact

    Description Europe Date Unsubscribed

    1. 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!

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

    Was this answer helpful?

    0 comments