Subscript Out of Range error when importing into Access 2007 from Excel.

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!)

4. 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

5. 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.

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!)

4. 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

5. 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.

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.

I think that you post was directed at me so here is my reply. First of all, thank you for replying to my post. Secondly, I was following the original post because I sometimes encounter the same type of problem as ccento. I did not post a question since the original post seemed to focus on the same type of problem that I have encountered. Yes, I reviewed the entire thread prior to contributing to the thread. Although it seems to me that I have encountered the same problem or a similar problem as ccento, I don't know if the reason for the problem that I have encountered is the same as the reason for the problem that ccento has encountered. The problem has ceased for me. I'm not sure, but I think that my problem may have had something to do with either the data in my source (Excel file) or the labels for my column headings. I very carefully made sure that the labels for my column headings were acceptable to Access. Then I updated my data source file to use the exact same headings that I used in Access. Then I made sure that there were no empty columns to the right of my last legitimate column and no empty rows below my last legitimate row in my data source file. This seemed to fix the problem for me. Now I can quickly and easily import data from my Excel source file into my existing Access table without any difficulties.

Thanks for your help.