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
    2015-08-05T14:09:45+00:00

    Closing both Excel and Access programs fixed my "Subscript out of range" error. Thanks for your insight.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-07-23T13:01:55+00:00

    For me this issue "Subscript out of range" error came up with Access 2010 when attempting to import a Microsoft Excel Binary Worksheet. "xlsb" using the import wizard. Make sure to review the properties as well on the file prior to import to make sure

    For me, it was solved when I saved the excel workbook again as a Microsoft Excel Worksheet. "xlxs" . Make sure you use SAVE AS instead of just renaming the file extension, as that will not work.

    Another way to solve it is to save the worksheet in question as a Text delimited file type.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-04-28T19:59:28+00:00

    Restarting Access also worked for me, when nothing else seemed to resolve the issue.  I had been importing Excel files into one table, and got the subscript error when attempting to import an Excel file into a different table.  Upon restarting Access, the error no longer appeared.

    Thanks!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-01-07T18:44:02+00:00

    When I got the "Subscript our of range" error, I had initially exported an Excel file out of Access to make sure my column names were right, clearing the old data and then pasting my Excel data into this template. 

    I was able to solve the "Subscript our of range" problem by copying the "formatting only" from a sample exported line over my new data.

    Was this answer helpful?

    0 comments