Subcript Out of Range

  • Thread starter Thread starter Lachlan
  • Start date Start date
L

Lachlan

I am trying to import from EXCEL (97) into a access 97. I
have set up columns in access corresponding to the excel
spreadsheet and tried to import from the spread sheet.
Whenever I do it I get 3 access error pop ups
saying "Subcript Out of Range" followed by a pop up
with "Type Mismatch". Then a window called "mx_FrmMain :
Form" appears.

Can anyone tell me what is going wrong hear?
Thanks in anticpation.

Lachlan.
 
Hi Lachlan,

A couple of things to check:

-Make certain that there are no values in any cell in the worksheet that
are incompatible with the data type you have assigned to the
corresponding column in Access. (You can sometimes search more
effectively for these by setting up formulas in a nearby Excel column,
along the lines of IF(ISNUMBER(B2),"","<<NOT A NUMBER")

-Make certain that Access isn't trying to import more columns than there
are in the table you set up. (By default, Access tries to import the
UsedRange of the Excel sheet; try defining the cells you want to import
as a named range and import that.)

-Check for hidden columns or rows in the range you want to import. These
can cause problems. So can formulas: Access can usually import the
values, but sometimes fails.

Sometimes, it's best to import the data to a new table (allowing Access
to define the field types); then use an Append query to move the data to
your table, doing type conversions if necessary.
 
Back
Top