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: Most helpful
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-11-13T01:56:14+00:00

    Hi Gina,

    I was experiencing this "subscript out of range" error on Access 2007, Windows 7, but I fixed it by resetting the column formats in Excel.  (Details below, in case anybody was making the same mistake I was).  Thanks for the help!

    So now I have a related question.

    In general, my workaround for Excel import/append errors (of which I get many) is to import the table under a new name, delete the original table, then rename the newly-imported table to match the original table's name:  this saves me having to re-create all relationships again.  This probably isn't "best practice" and I fear it may be causing unseen problems somewhere in my database... can you suggest a better method?

    (FYI) Details of the solved problem:

    I exported a table from Access into Excel in order to edit a large amount of data quickly.  I changed the primary key values (which are ID auto-number) of these records in order to import/append to the table from which I'd exported them, thinking I'd delete the original records once I got the newly-edited records imported. 

    My answers to the questions in above thread:

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

    sheet?  **HERE was the problem!  I set all Excel column formats to show Text, Date, Number, etc (they had been set to "General") and the import/append worked!)

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

    Wildcard Characters) that might confuse Access?  NO

    http://allenbrowne.com/AppIssueBadWord.html

    1. Have you tried it manually? (You know, File... Import...)  THERE is no "file" command on the ribbon?  But I am able to import this spreadsheet as a new table without getting the error, if that's what you're asking.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2010-11-05T16:42:14+00:00

    Plansforgood...

    Thanks for the follow up and glad the problem for you is resolved...

    --

    Gina Whipp

    2010 Microsoft MVP (Access)

    Please post all replies to the forum where everyone can benefit.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  4. 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