Solved

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

Posted on 2011-03-02
6
1,226 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 500 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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