?
Solved

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

Posted on 2011-03-02
6
Medium Priority
?
1,249 Views
Last Modified: 2012-05-11
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
Comment
Question by:donnie91910
6 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 2000 total points
ID: 35023255
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
 
LVL 3

Expert Comment

by:chris2506
ID: 35023277
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
 
LVL 5

Expert Comment

by:Pabilio
ID: 35024119
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35025890
How exactly are you importing it?
0
 

Author Closing Comment

by:donnie91910
ID: 35028317
I converted to .csv and worked well.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35028590
Cool!  sometimes we have to do intermediary translations to get these things to talk right!
Thanks for the "A" :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question