"Todd Huttenstine" wrote ...

The following code writes to a closed workbook because its
treated like a database. However, this code only
specifies the sheet to update. Is there anyway I can
specify the sheet and the cell to update?

szSQL = "INSERT INTO [Sheet3$] VALUES('TestValue1'); "

To update a single cell in an existing table you use HDR=No in the
extended properties of the connection string and use UPDATE sql syntax
such as:

UPDATE [Sheet3$A2:A2] SET F1='TestValue1';

F1 is the default name Jet assigns to the first column where the
column name is unknown or invalid. The inserted value must match the
data type for the *whole* column. Better to use a key column with
HDR=Yes e.g.

UPDATE [Sheet3$]
SET MyDataCol='TestValue1'
WHERE MyKeyCol=55
;

INSERT INTO is used to add a row to a table. With Excel, it is always
added to the end of the existing table, subject to having enough space
for the new row.

You can use INSERT INTO to put a value into a cell but AFAIK this only
works on a sheet without an existing 'table' e.g.

INSERT INTO
[TotallyBlank$A1:A1]
(F1) VALUES ('TestValue1')
;

will put the value into cell A2 on an existing totally blank sheet
named TotallyBlank. Did I say cell A2? Sure did. The only way I've
found to insert the value into A1 is:

CREATE TABLE
TotallyBlank
(
TestValue1 VARCHAR(255)
)
;

This puts a column header into cell A1, in a new defined Name named
TotallyBlank on a new sheet named TotallyBlank (unless sheet
TotallyBlank already existed, then it would create a new sheet named
TotallyBlank1).

Jamie.

--