Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Update
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Update
"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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update one worksheet tab to automatically update all other tabs? | Excel Worksheet Functions | |||
Data Validation lists update orginal cell with list update | Excel Worksheet Functions | |||
i want to update one excel file the other one update automaticaly | New Users to Excel | |||
keep value from last update and don't ask to update links | Excel Discussion (Misc queries) | |||
Date update on data update | Excel Programming |