Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERT into named range using ADO
Hi,
I've got a spreadsheet with several named ranges that I'm going to dump some data in from SQL server. I've got most of it working, but I've run into one problem! 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. Is this possible? If so, how would I accomplish this? Regards, Tim. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERT into named range using ADO
What does your current code look like?
On Thu, 27 Jan 2005 18:47:25 -0000, "Tim Payne" wrote: Hi, I've got a spreadsheet with several named ranges that I'm going to dump some data in from SQL server. I've got most of it working, but I've run into one problem! 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. Is this possible? If so, how would I accomplish this? Regards, Tim. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERT into named range using ADO
INSERT INTO MyRange (col1, col2) VALUES ('value1', 'value2')
Cheers, Tim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Named Range Into a Cell | Excel Worksheet Functions | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Insert named range problem | Excel Programming |