Categories

Main
Database
Java
Microsoft.NET
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
ColdFusion
Crystal Reports
Delphi, C etc
JAVA
Microsoft.NET
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
XML & XSLT
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
Applications & Tools
Database Concepts & Design
EJB programming & troubleshooting
EJB design
General J2EE
XML & Web services
Web tier: servlets, JSP, Web frameworks
Performance and scalability
Industry news
TSS feedback
Mobicents Contributors
Mobicents Users
JSLEE Resource Adaptor Types
Planning JavaOne 2008
Sun Tech Days
Other Java conferences
Binary Web Services and XML
Metro and JAXB
GlassFish
GlassFish Plugins
Project jMaki
GlassFish WebTier
Mural
Java Development Tools
Java WS & XML Community News
JAXP
Java SE
6uN Early Access
Java Quick Starter
Java SE Snapshots: Project Feedback
JCK Forum
Feedback and Suggestions
JDK Distros
JDK Open Source
General JSR Discussion
JCP 2.6
JXTA Community Forum
ME Interest
ME Cool Apps
ME General Help
ME Feedback & Suggestions
ME Application Developer Interest
Blu-ray Disc Java
ME Developer Days
Squawk
Mobile Developer Alliance
OpenCable
LWUIT
JavaFX Script Language Discussion
OpenJFX General Discussion
Scene Graph
General Performance Discussion
Your Java Career
NetBeans 6.0
Servlets
JSP
JSF
Portals and Portlets
EJB and Other Java EE Technologies
Distributed Java
Object Relational Mapping
JDBC
Web Services
Swing / AWT / SWT / JFace
JNLP and Web Start
Java Micro Edition
Sockets and Internet Protocols
Threads and Synchronization
Performance
Applets
I/O and Streams
Other Java APIs
Game Development
Java in General (beginner)
Java in General (intermediate)
Java in General (advanced)
Programmer Certification (SCJP)
Developer Certification (SCJD)
Associate Certification (SCJA)
Web Component Certification (SCWCD)
EJB Certification (SCBCD)
Mobile Application Certification (SCMAD)
Architect Certification (SCEA)
Web Services Certification (SCDJWS)
XML Certification
Product and Other Certifications
Mock Exam Errata
Sun Certification Results
Authors' Corral
Book Reviews
Events
Bunkhouse Porch
Teachers' Lounge
Testing
OO, Patterns, UML and Refactoring
IDEs, Version Control and other tools
Ant, Maven and Other Build Tools
Linux / UNIX
Mac OS
HTML and JavaScript
XML and Related Technologies
Agile and Other Processes
General Computing
Security
Groovy
Scala
Other Languages
Struts
Application Frameworks
Other Open Source Projects
BEA/Weblogic
IBM/Websphere
Oracle/OAS
Apache/Tomcat
JBoss
Other Java Products and Servers
JavaRanch
Cattle Drive (java college)
Moderators Only
Trash Can
Jobs Offered
Jobs Wanted
Jobs Discussion
Meaningless Drivel
Programming Diversions
Blatant Advertising
Java Announcements
New To Java
Advanced Java
Java Applets
Networking
Threads and Synchronization
Java 2D
AWT / Swing
SWT / JFace
CLDC and MIDP
CDC and Personal Profile
Sun Java Wireless Toolkit
Enterprise JavaBeans
JavaServer Pages (JSP) and JSTL
Java Servlet
JavaServer Faces
Web Frameworks
Database
XML
Lucene
NetBeans
Eclipse
IntelliJ IDEA
JCreator
Other IDEs
Java Tutorials
Java Tips
Jobs Discussion
Jobs Offered
Jobs Wanted
Professional Certification
Forum Lobby
Java Blogs
Introductions
Reviews / Advertising
Suggestions & Feedback

Resources

Java Database
Linux
Coding
Mobile
Hardware
Software Development
Software Development
iOS,OS X
iOS,OS X
ORACLE
IBM DEVELOPER
IBM DEVELOPER
MSDN
MSDN


Tags

Microsoft Access

The database that comes with Office Professional. Access can handle tens of thousands of rows with ease and has great connectivity support for traditional database servers.

Subscript out of range when importing Excel into Access


I have a couple of fairly large Excel worksheets (within one spreadsheet) that I am trying to import into Access. The import wizard says "Subscript out of range" and does not complete the import. I know that this error may have something to do with the size of the array, but I'm unsure what that means.

Could it mean that I'm trying to import too large of a worksheet? The worksheets have between 2600 and 2800 lines/records each.

Any help would be appreciated.

Thanks!

Tim

there is very little chance that you can get something into excel that cannot be imported into access... at least as far as the number of rows is concerned.

questions:

how many columns?

how are you importing (from .XLS or .CSV)?  --- sometimes it pays to take an XLS to CSV before the import to MDB.

are you importing in code or from the menu (i.e. is it your code that is "running out of subscripts", or an access-internal problem)?

zy

I may have solved at least this problem by splitting one of the worksheets in half and importing just that portion of the data into Access. When I tried it this way, I had no problems with the import.

However, I just now see the message from izy to try with a CSV file.

I have 18 columns, and I'm importing from the menu.

I will report back.

When I try CSV, not all the data gets imported. There are a number of errors in several different fields that show up in an ImportErrors table.

great! you solved the problem.

meanwhile, i don't see why splitting the XLS in two changed anything! every month i import a few thousand rows of .CSV sales stats with 12 fields. (actually i don't import, i link to the .CSV and use code to check/reformat/append the data to my master table). i don't get a subscript issue.

the reason i prefer .CSV is the Advanced button on the import dialog - you get some control over how the fields will be handled and you can save the import spec to use with multiple (same format!!) spreadsheets. importing from .XLS lets A make all the decisions.

linking then appending to an existing table (via query or via code) may also be a better strategy - you gain control over field type/size and have a reformatting opportunity. i generally specify all the .CSV fields as text (Advanced button) to minimise the chance of import errors and then reformat in code to suit my table.

18 fields is not an issue, hundreds of fields could be.

...but your problem has gone away. fine!

izy

Izy:

I redid the CSV import and defined all fields as text, as you suggested. 

The CSV format dropped out the leading zeroes on a couple of fields, but I was able to restore the zeroes with update queries.

Then I redefined one field as number in order to use it in linking to another table (my original intent).

The major outcome is I did not get the "Subscript out of range" error with the CSV import (when I followed your suggestions.)

This procedure sure beats all that monkey business with splitting the Excel file.

Many many thanks!

Tim


Related Links

Forms
Timers
Count How Many Items Contain Certain Value
Query using wildcards?
Number and Text Field in a querry
Really need some help- I am stumped!!!
Subtotal help please!!!???
Report Footer Totals for independent fields
AddFromFile reference problem
Error Handling Code...
Query Problem... please help
Guruischool ...
UDFs in ADP files.
Changing Query Criteria
Merging records
Crosstab Sorting Order