Error: Cannot expand named range

[bullpit]

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')";

 

[Barb Reinhardt]

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]

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

Thanks a lot Barb for your reply. I have been trying OFFSET all day long
yesterday. Heres the situation. I insert records using an embedded SQL query
in my code. This is the SQL query:

OleDbCommand cmd = new OleDbCommand("Insert into [Range1](PARTNUMBER,
DESCRIPTION, QTY) values ('PART','PART','1')");

I name the OFFSET range as DynaRange. So in all I have two ranges, one for
the columns I want in my range (Range1), and the other to exapand Range1
dynamically (DynaRange). If I just try to use DynaRange in the query, the
driver does not recognize it. If I try to use Range1 and assume the DynaRange
will expand the range, it doesn't.

I did have two more field names in cells A10 and B10 but I deleted them just
for testing.

I just want the query to work as it does when the field names start from the
top-left cell (A1) and the whole excel sheet is treated as a table (with no
ranges used) and keeps expanding automatically as I add records.

Please please help....
Thanks a ton.

 

[Barb Reinhardt]

I can help with the dynamic range, but I am not an expert in the query part.
I'm afraid I can't assist at this point.

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

Thanks a lot Barb for your reply. I have been trying OFFSET all day long
yesterday. Heres the situation. I insert records using an embedded SQL query
in my code. This is the SQL query:

OleDbCommand cmd = new OleDbCommand("Insert into [Range1](PARTNUMBER,
DESCRIPTION, QTY) values ('PART','PART','1')");

I name the OFFSET range as DynaRange. So in all I have two ranges, one for
the columns I want in my range (Range1), and the other to exapand Range1
dynamically (DynaRange). If I just try to use DynaRange in the query, the
driver does not recognize it. If I try to use Range1 and assume the DynaRange
will expand the range, it doesn't.

I did have two more field names in cells A10 and B10 but I deleted them just
for testing.

I just want the query to work as it does when the field names start from the
top-left cell (A1) and the whole excel sheet is treated as a table (with no
ranges used) and keeps expanding automatically as I add records.

Please please help....
Thanks a ton.

 

[bullpit]

A quick note:
Range1 includes fields PARTNUMBER, DESCRIPTION, QTY (cells C10, D10, E10)
for now (will be adding more if all this works).

DynaRange, when I check it, includes only column C starting from C10. I also
tried using all the 3 columns in DynaRange but that does not work either.:-(

 

[bullpit]

Thanks again for trying Barb. I really appreciate it.