SQL Update

[Todd Huttenstine]

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?

Dim objConn As ADODB.Connection
Dim szConnect As String
Dim szSQL As String
' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\QTD\Sales.xls;" & _
"Extended Properties=Excel 8.0;"
' Create the SQL statement.
szSQL = "INSERT INTO [Sheet3$] VALUES('TestValue1'); "
' Create and open the Connection object.
Set objConn = New ADODB.Connection
objConn.Open szConnect
' Execute the insert statement.
objConn.Execute szSQL
' Close and destroy the Connection object.
objConn.Close
Set objConn = Nothing


Thank you
Todd Huttenstine

 

[Jamie Collins]

...

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.

--