• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1256
  • Last Modified:

Subscript out of range error when I try to import and Excel spreadsheet into Access table.

I have received an Excel Spreadsheet from one of my users.  I get the error "Subscript out of range" when I try to import into an Access database.  Is there the possibility that there is some code imbedded in the spreadsheet?  And how do I find it?

Thanks.
0
donnie91910
Asked:
donnie91910
1 Solution
 
rspahitzCommented:
I don't think the Access import looks at Excel's VBA code.
Possibly you have a range in Excel that is pointing to a location that Access cannot pull out?

Usually when you get import errors, Access will create a new error table and put the problem cases there.  Did you check for that new table?

Another option is to export the worksheet as a CSV file then import it into Access to see if that works better.
0
 
chris2506Commented:
Hi,

I don't think this is an issue with 'embedded code'. Try working through the following steps:

1. Ensure there are no hidden columns / rows

2. Ensure that the data in each column is all of the same type (for example, if you have a column of numbers make sure that they are all formatted as such and some are not stored as text)

3. If importing to an existing database in Access, ensure that the data types in the Excel columns match those in the corresponding database columns.

If the above doesn't help, it may be worth copying and pasting the Excel data into a new workbook and trying the import again. This would eliminate the possibility that 'empty' cells have previously been formatted/stored data and are still being seen by Access as active.


Hope this helps,

Chris
0
 
PabilioCommented:
Maybe you are having problem with the Key field ?... which does not accept repited values ?
If you are importing the values to an existing db check wich field is the key field and look for repeated values in that field in the spreadsheet.
Hope it helps,
Roberto.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rory ArchibaldCommented:
How exactly are you importing it?
0
 
donnie91910Author Commented:
I converted to .csv and worked well.
0
 
rspahitzCommented:
Cool!  sometimes we have to do intermediary translations to get these things to talk right!
Thanks for the "A" :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now