import data from Excel - "subscript out of range"

[kul]

Hi there,

I was trying to import a spreadsheet from Excel into Access. There are
around 30,000 rows and 17 columns. Could it be a problem that causing the
message "subscript out of range" ?? What does that message mean?

In fact, there are 3 similar files need to be merged (same kinds of columns)
to become a giant database. I was trying to import and append the first
spreadsheet but got stuck already. Can Access handle that?

Thanks.
kul

 

[Golfinray]

The problem is probably not in Access, but Excel. Either there are some
numbers too long in Excel or wrong datatypes. Try LINKING the table and then
copy and paste into a new table.

 

[Tom Wickerath]

You might also try ensuring that the Excel spreadsheet does not include any
illegal fieldnames as header rows. Check for the use of special characters,
such as apostrophes, colons, etc. in the header row in Excel.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[kul]

Thank you Golfinray and Tom. I'll check what's bothering me here. Thanks again.

 

[Tom Wickerath]

I think (?) another "gotcha" can be having a leading space in the name of a
column header, in Excel. So, check for those as well. I also recommend
defining a named range within Excel, so that you just establish a linked
table with a named range. This way, you're not allowing the Access import
wizard to guess where the data ends--you've already precisely defined it
using a named range within Excel.

In general, you will have better luck linking to Excel data, and then basing
an Append query from the linked table to a pre-defined table within Access.
This way, you are not allowing the import wizard to guess on the field type.
It also allows you to use Type Conversion Functions, as required, to coerce
the data into the correct type. An example would be a date value represented
as a string. You can use the CVDate function to force this into a Date/Time
data type.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[kul]

Thanks for your suggestions.

Yeah, I guess if I have to look into the fields which has got more than
enough empty space. I'm gonna die not even half way in the spreadsheet.

I've checked the column header and make sure there isn't any leading space.
I've also changed the names (though I'm not 100% sure what's a "name range".
But I try to simplified the header which was a pretty long one before. I hope
that helps. However, I still have the "subscript out of range" when I try to
finish importing the data.

The spreadsheet was done by somebody before. But I've tried to changed the
types (eg. date info converted into date format). Do that help? What about
zip code? There are 2 columns at the zip code. Do you think I should change
the format into zip code numbers at the Excel file? But it will be a trouble
as the extension is placed separated at another column.

Thanks again.

 

[Tom Wickerath]

Hi Kul,

I've also changed the names (though I'm not 100% sure what's a "name range".

I found the following in the Excel 2003 Help file (sorry, I'm not using
Excel 2007):
Topic: Name cells in a workbook
Subtopic: Name a cell or a range of cells

Select the cell, range of cells, or nonadjacent selections that you want to
name.
Click the Name box at the left end of the formula bar.
Type the name for the cells.
Press ENTER.

Note You cannot name a cell while you are changing the contents of the cell.
_____________________

Make sure that the total path length (complete filename) does not exceed 218
characters. Excel reserves some characters for the worksheet names, so you do
not get the normal maximum of 255 characters.

Try the following experiment:
Create a brand new Excel spreadsheet, with some reasonable names for each
column.
Add some data of various data types (text, date, numbers) into different
columns.
Close the spreadsheet. Open a brand new Access database.
Can you import this data?

If the answer is "yes", then move on to the second test, below. If the
answer is "no", then you may have a corrupt accwiz.dll file, or an improperly
registered .dll file, such as the same accwiz.dll or perhaps dao360.dll. Oh,
by the way, which versions of Access and Excel are you using? Which service
pack are you running?

Test # 2 (to be run only if Test # 1 is successful)
Try exporting the data from Excel to a comma separated variable (*.csv) file.
Then use File | Open in Excel, and try bringing the same data back into Excel.
Save the new spreadsheet. Can you import it now?

Note: You might also try directly importing the text file into Access.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________