none
Cannot expand named range RRS feed

  • Question

  • 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:

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

     

     

     

     

    Tuesday, June 12, 2007 3:46 PM