ExcelBanter - INSERT into named range using ADO

Tim Payne wrote:
In some of the ranges, I don't want the first block to be treated
as the headers. For example. I have the first two cells of the
spreadsheet
A1 and A2 as a named range called 'Titles'. Into these two cells I am

inserting a name and date. I would like to be able to insert directly
into
the top row of the named range, not have it insert the data beneath
the
first row in the range.

In standard SQL, the INSERT INTO command provides no means to specify
the position of the new row. This is because, in SQL terms, the
physical order of rows is immaterial; you must use an ORDER BY clause
to guarantee a particular row oroder.

That's said, for performance reasons, most DBMSs support the concept
of a clustered index which determines the physical (i.e. on disk)
and/or logical (i.e. pointer chains) location of a new row, but this
will differ between products. For example, a MS Access/Jet database
(e.g. a .mdb file) table is stored in order of the primary key but is
only physically reordered when the file is compacted; subsequent new
rows are added in date/time order i.e. effectively appended to the the
'bottom' of the table with logical order being maintained by the
primary key's index.

Excel also uses Jet and exhibits the same behavior for INSERT INTO i.e.
it is physically added to the 'bottom' of a range. However, Excel lacks
the Jet functionality of compacting and indexes. Because Excel does not
support DELETE either, this means the row will always retain its
position. This is a good thing for Excel e.g. think of the effect of
adding a new row mid table would have on dependent cell formulas.

If you need to change the physical order of the rows using SQL, you
could use a SELECT..INTO..FROM to create a new table (defined Name on a
new sheet) and issue a DROP TABLE on the original. Changes made to the
xls with something other than pure Jet is another matter of course e.g.
retaining the sorted data in memory using an ADO recordset, issuing a
DROP TABLE (or similar) and using CopyFromRecordset to repopulate the
original table.

All that said, I think you may be able to use an UPDATE rather than an
INSERT INTO. Excel has a pleasing loose definition of 'table' and it is
not limited to 'named ranges'. You can specify a range address e.g. you
may be able to use something like:

UPDATE [MySheet$A1] SET F1='value1';
UPDATE [MySheet$A2] SET F1='value2';

You will need to specify HDR=NO (no header row) in the extended
properties of your Jet connection string i.e. in the same place you
specified Excel 8.0. Without a header row, Jet uses default columns
names of F1, F2, F3 etc. This relies on the sheet name and cell address
of your target range being predictable. Note the data type of the new
values to these 'one row' tables must still match the data type of the
column when the table is considered as being the worksheet i.e. cell
values above and below the UPDATE range may have an adverse effect on
the determined data type.

Jamie.

--