Tried to export from excel to Access, but errors shows "Subscript out of range"

[radicrains]

hi everyone.

As mention above.. I've tried to export the company's database from Excel into Access, however an pop-up message pops up saying "subscript out of range"



Anyone has any ideas?

I've tried to google this problem before coming here, and some says that we need to convert it to .cvs file... I've tried, it works well for the 1st tale, but not the second and so on..

any ideas how i can overcome this?

thanks in adv..

[ajetrumpet]

most likely it has nothing to do with a file extension.

the times that I've seen people post about this message, the cause is related to the default field types that Access uses when it reads the data coming in. I can't remember what Access does, but I remember people saying that it looks at the first 3-5 rows of data to determine a proper type for assignment to the field.

I'd bet a dollar that's the problem here as well. Take a look at your maximum numbers in Excel, in your numeric columns. For example, a column that starts like this:

• 39
• 540
• 38
• 20
• 400

but ends like this:

• 20039372
• 2999028374
• 34
• 3

would most likely throw the same error. Not 100% sure, but I would bet. Other data types may be a problem too. I think I remember alphanumeric mixtures also cause problems.

[radicrains]

Hi,

I am actually aware that MS Excel has direct formulas that do not work on MS Access. Hence, before i exported the file, I have copy column by column of the data i need and "paste special" on a new workbook...

This paste special allows me to choose to paste the "value and format" that has appearred in the box, w/o the formulas and etc. Hence it is as good as a "fast data entry" method..

That was what i did.. if there's other ideas, please state, I'm willing to experiment...


thanks in adv..

[pkstormy]

Try saving the xls file as a csv file and then importing the csv file. Otherwise I'd suspect that you've got a bad value somewhere in the excel spreadsheet (or a lingering value at the bottom of the excel spreadsheet somewhere and when MSAccess imports it, it's expecting a different kind of value (ie. you've got date values in a column but one of the dates is 12/33 or something like that versus 12/01/2010.) If you copied/pasted all the columns, a value somewhere at the bottom of the excel spreadsheet might also have gotten pasted.

Keep in mind that when MSAccess imports an xls file, it tries to interpret what the field type is for the xls column based on the first row of values it sees. So if your first row of data for the first xls column is something like 123, MSAccess may think this is supposed to be a number type. If it sees something like 12/22/2010, it will think it's supposed to be a date field type. As ajetrumpet stated, values that might be buried elsewhere in the excel column could cause a problem based on what the MSAccess import interpreted that column's field type should be.

I'd look at the values on the spreadsheet and see if any of them look like they would cause mis-intepretation when importing. You could also design an Import Specification in MSAccess and then import the csv file to make sure the field types are setup correctly when importing.

If you get this kind of error when importing 'any' xls file (even a simple one with only a couple rows of data), then check your MDAC which has the driver for excel importing.

[pacittk]

Thank you so very much! This was the problem. Some of my formatting had changed and after I streightened out all the formatting my record finally came and appended the old record! Everything has to match!!