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.
donnie91910Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rspahitzConnect With a Mentor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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.

All Courses

From novice to tech pro — start learning today.