UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Excel to Access Import - Subscript out of range    
 
   
bakersburg9
post Oct 14 2009, 07:42 PM
Post #1

UtterAccess Ruler
Posts: 4,375
From: Downey, CA



This is a troubleshooting question - trying to import Excel v2007 file into .accdb, into an existing table, and I get a "subscript out of range" error
Any ideas ? All the fields match
Go to the top of the page
 
+
GroverParkGeorge
post Oct 14 2009, 09:07 PM
Post #2

UA Admin
Posts: 22,651
From: Newcastle, WA



Not enough information.
How are you doing the import? Code, via the manual file import process? A query?
What does it mean to say "all the fields match"?
It's likely to be an invalid value in one field in one row, but that is just an guess at this stage.
Go to the top of the page
 
+
bakersburg9
post Oct 15 2009, 10:51 AM
Post #3

UtterAccess Ruler
Posts: 4,375
From: Downey, CA



It’s an excel file we get once a month, and I’m trying to import the data from one of the worksheets with about 23 columns of data. I checked, and all the column header names match the field names in the destination table in Access
The only field that literally is in the .mdb that is not in the Excel worksheet is the first field in the table, ID (AutoNumber)
I’ve attached the file
Attached File(s)
Attached File  SubscriptOutOfRange.zip ( 22.46K ) Number of downloads: 11
 
Go to the top of the page
 
+
GroverParkGeorge
post Oct 15 2009, 01:25 PM
Post #4

UA Admin
Posts: 22,651
From: Newcastle, WA



Check your LOB_IND_DBD column, which contains a mix of digits and alpha characters. Is it coincidental that every record which has a "9" in that column has a blank (probably null) in the EAR_CATG_DBD column?
may be wrong, but that's where I'd be looking first-- columns EAR_CATG_DBD and LOB_IND_DBD. Mixed data types (possibly) and Nulls.
HTH
George
Go to the top of the page
 
+
ChidesterSr
post Oct 17 2009, 07:56 PM
Post #5

New Member
Posts: 13
From: CT USA



This one way I have done it, but please Mr George feel free to shoot it down ASAP if wrong, but I do it monthly for somewhat the samething. In Access I use the import from the Excel workbook, what ever sheet I need and save it in a table (Sheet 1 or what ever.) Take that table and query the information to the Access table as an append procedure. I now have added the data to my table. I of course check the query to make sure all the column headings from Excel are the same as the feild names are in Access. Even if they are not, then I use the down arrow to select what field that refers to in Access. Once I have saved the query then each month the query is run and the data is saved.This is October and I have 10 months so far no problems.
Go to the top of the page
 
+
GroverParkGeorge
post Oct 17 2009, 08:06 PM
Post #6

UA Admin
Posts: 22,651
From: Newcastle, WA



That's how I do it too. Generally speaking, once you've created the import successfully once, and save the import/export spec for it, it's straightforward to repeat.
I am thinking that once he gets his data properly sorted out, it will be the same here.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 11:36 PM