Error: Cannot expand named range
 

Hi,

I am trying to insert some records into an excel sheet thru Named range.

I could have used the Sheet name but the field names actually start after
skipping first 10 rows (reserved for a header) so the ADO driver does not
recognize the table if I dont use a named range. Here are the details:

Name of range: Rang1
Range Formula: =Product_Pricing!$C$10 : $E$10
The 10th row contains the field names. Right now, it only has 3 fields.

When I insert a record using an SQL Insert, it inserts the records fine but
when I try to insert another record, it gives me this error: "Cannot exapand
named range".

I know why it gives the error, but I want to know if there is way to expand
the range as the records are added.

I could accomplish this using an unnamed range and keeping a track of the
current range using a counter in my code, and increment it everytime a record
is added but this approach is not flexible. I have to insert a value for all
the columns in that range, and I cannot use an SQL query like this where I
can specify the field names:

INSERT INTO [Product_Pricing$C" + i + ":E" + i + "](PARTNUMBER, DESCRIP,
QTY) VALUES ('Part1', 'Description','1')";

Error: Cannot expand named range
 

Why not use an OFFSET function to get your range

Assumptions:

THe only data in row 10 is is field names
THere is nothing in cells B10 or A10. If there is, a change will need to be
made

=OFFSET(Sheet1!$C$10,0,0,COUNTA($10:$10),1)

You may need to subtract something from COUNTA if there is something in A10
or B10.

HTH,
Barb Reinhardt


"bullpit" wrote:

Hi,

I am trying to insert some records into an excel sheet thru Named range.

I could have used the Sheet name but the field names actually start after
skipping first 10 rows (reserved for a header) so the ADO driver does not
recognize the table if I dont use a named range. Here are the details:

Name of range: Rang1
Range Formula: =Product_Pricing!$C$10 : $E$10
The 10th row contains the field names. Right now, it only has 3 fields.

When I insert a record using an SQL Insert, it inserts the records fine but
when I try to insert another record, it gives me this error: "Cannot exapand
named range".

I know why it gives the error, but I want to know if there is way to expand
the range as the records are added.

I could accomplish this using an unnamed range and keeping a track of the
current range using a counter in my code, and increment it everytime a record
is added but this approach is not flexible. I have to insert a value for all
the columns in that range, and I cannot use an SQL query like this where I
can specify the field names:

INSERT INTO [Product_Pricing$C" + i + ":E" + i + "](PARTNUMBER, DESCRIP,
QTY) VALUES ('Part1', 'Description','1')";