Another "Subscript Out of Range" Problem

  • Thread starter Thread starter Graham Naylor
  • Start date Start date
G

Graham Naylor

Hi,

I am also getting a "Subscript Out of Range" error not not in a report but
when I'm importing a spreadshet directly, I have a number of similar
spreadsheets all have the first line as a header, rach is imported into its
own table, all work except one which errors every time?

The sheet imports with no problem into a new table just to make thing even
more confusing.

What is it likely to be?

Thanks for any help

Graham
 
"Subscript Out of Range" typically occurs if a member of an array is out of
the bounds of the array. The reason it will import into a new table is that
you are newly defining the range, when you import it. Try, importing into a
new table, then using that same table to import again.
 
Graham,
This is usually caused by having too many pieces of data in one record (or more) during
the import...
Since all the other speadsheets import just fine, I would expect there's one with a
"finger check" record somewhere.
12.2, ABC, 77,NH
14.7, ABCDE, 66, MA
22.1, XYZZ, 54, CA, Hello there 'this record has 5 elements
24.8, XzAA, 62, NY
etc...
At the worksheet, make sure any columns other than the ones you want imported, are
cleared (delete any values in all other columns)
Make sure any rows above and below your import data are truly empty.
Try exporting the worksheet to a txt or csv file, and search through that for the
error. If something is hidden, and being imported, it "should" show up in an ascii text
file.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
I've tried the following

1 - Imported into a new table
2 - Cleared the data from this table
3 - Imported into this table (same spreadsheet & data)
4 - Same Error at the ends of the Wizard

Help, I'm now really confused
 
Hi,

I tried exporting to a CSV file and checking the data, all looked OK. Just
to be sure I reopened the exported file in Excel (by now any formatting
should be lost?) and everything appeared in the correct column with no extra
colums.

Are there any characters that I should avoid using in the headers or data?

Graham
 
Rather than "define" what can or can not be in the header, or in the data for that
matter.... while shooting this bug... just make sure you have just text in there (for
now), and see if that makes a difference.
I would assume that any Access special characters should be avoided, like , # ! " '
etc... (particularly check any text fields for "," commas, since they designate a "next
field" in comma delimited files.
We've established that all other imports work fine, what is it about this file that is
preventing successful importation?
Suggestions... try the import after each of these steps
Make sure your headers are vanilla... just text.
Make sure the first 20 recs have no chars other than Text and Numbers
Try just importing the Header and 20 lines of data. Does that work?
Try importing only the first 1/2 of the data. Does that work?

You'll just have to chop this import up in to pieces, and try to "drill down" to where
the import is failing.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
Finding a "bad" record is a time-consuming process. 1 character to avoid is
the # sign, particularly as the first character of any field or record.
Access uses the # to mark errors and corruptions.

To find a find record or page of records, divide them in have, working with
the last half first and do the import. If that breaks, divide in half again,
also working with the second half of the records first. With a lot of
records, it can take from 1 to 2 hours to find the "bad boys" but you'll
find them. The reason that you always work with the second half first is
that, in theory, the records are laid down in the order of creation and the
chance of the error not showing up in an earlier record is less than a later
one.

I haven't had very many problems that required this kind of diagnosis, so I
can't speak from sagely experience. In almost 15 years of working with
Access, I've had less than 8 or 9 problems other than during development.
The important thing here is that whatever plan you decide to do, be
consistent.
 
Back
Top