How do I deal with the "subscript is out of range" message.

[AuditIA]

This happened when I tried to append an excel file to a "structure only" copy
of last month's file. I've done this many time before, but now I'm afflicted
with this "subscript is out of range" message that stops me dead in my
tracks. What do I do BESIDES import the data as a new table and change all
the attributes?

 

[Jackie L]

The problem is that you have no control over the data coming from Excel.
There is no way to be assured that it is in the same format as the previous
file. I would suggest importing it into a separate table and then append to
your table(then you can force fields to be the correct format). Otherwise, I
would save the Excel file as .csv or .txt and then you can create import
specifications to format the data before importing. You will still have the
append issue.

You should be able to automate the process through code if needed and then
you can also delete the temp tables that are imported. Changing the
attributes on the new table will not necessarily work if the data does not
fit the data type you are giving it. That is why I suggest appending to your
existing table instead.

 

[AuditIA]

Jackie,
Thanks for the reply.

I understand your suggestions and have used those methods. However, I have
done the append process I referenced below for about 6 months with success.
The text file is received, sorted, filtered, and exported from a program
called Monarch. It exports everything to Excel in a 2007 format which I then
import into my db. I have had no problems with this procedure - until
yesterday.

Why would this pop up when I have not changed my procedure and have been
successful in the past?

Any ideas?

 

[Jackie L]

It sounds like it is a change in the data. Why are you going from Monarch to
Excel? Monarch should be able to give you a .csv or .txt file. Going to
Excel in the interim can cause data to be altered by dropping leading zeros,
changing text to numbers, etc.

 

[AuditIA]

There are several things done in Excel. First, an account number contined in
the data must be rid of its dashes. (I know that can be done in Access, but
its easier to do while I have it in Excel.) Then, there are certain fields to
be deleted and/or moved to a new position. All this is easier to do in
Excel. Do you know Monarch? You use masks & templates to extract info from
reports that are in any number of file formats. It's very useful. I use it
for my 9 companies in exactly the same way using the same set of templates.
This time, one of the company's data didn't work (the message) and the others
did. I'm still stumped about why and what I'm to do.

Your statement that the data has changed is undoubltedly correct. I
appreciate your input.