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

I've experienced this on many occasions. No amount of editing or re-formatting of columns would fix it. 

I had a spreadsheet linked to the Access database that I would use for post-processing (pivot tables, charts, macros, etc). The raw import of new data would go smoothly until I started refreshing the data from the linked spreadsheet. Then, the Subscript out of Range error would start upon the next import of new raw data (during the same session). Only by closing both Access and Excel and re-starting them (not just the files, but the whole program) would the error stop. I think there is a connection between a data-link and the import process such that Access can't update a table if it is actively linked to some other source such as a spreadsheet.

I do not do my *formatting* in Excel, I set up an Import Specification and do it there.  I would try that and see if that fixes your issue oif delete/recreate.  Especially because it is bound to cause something to act hinky somewhere...

--

Gina Whipp

2010 Microsoft MVP (Access)

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

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.

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?

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.

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

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!

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