Hey guys

Ok I believe this is the correct news group I need to be in(I really hope
so)
I have been working on this problem now for 4 days. I am trying to simply
add a new record to a text spreadsheet. Its C:\TEST.XLS. It has 2 columns
(A and B) The name in A1 is AAA and B1 is BBB. Now from another workbook I
want to be able to add the record "testA" to column A and "testB" to Column
B. I have the following code but am receiving an error. See my code
below....

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=C:\TEST.XLS;" & _
"Extended Properties=Excel 8.0;"

' Create the SQL statement.
szSQL = "INSERT INTO Sheet1$" & _
"VALUES('testval1', 'testval2');"

' Create and open the Connection object.
Set objConn = New ADODB.Connection
objConn.Open szConnect
' Execute the insert statement.
objConn.Execute szSQL, , adCmdText Or adExecuteNoRecords
' Close and destroy the Connection object.
objConn.Close
Set objConn = Nothing


Now I am receiving the Automation Error on the following line of code:
objConn.Execute szSQL, , adCmdText Or adExecuteNoRecords

How do I make this code work?


Thank you
Todd Huttenstine

As far as I know the driver doesn't support updates/inserts/deletes on Excel
files. You can use the Excel COM library to manipulate spreadsheets from VB.

--
Jacco Schalkwijk
SQL Server MVP


"Todd Huttenstine" <t.huttenstine (AT) charter (DOT) net> wrote

Hey how would I do this with COM? Do you happen to have a sample code for
just adding to a closed workbook? Any help is greatly appreciated.


Thank you
Todd Huttenstine


"Jacco Schalkwijk" <NOSPAMjaccos (AT) eurostop (DOT) co.uk> wrote

I don't have anything handy, but if you ask for it on an Excel newsgroup,
you'll get something pretty quick.

--
Jacco Schalkwijk
SQL Server MVP


"Todd Huttenstine" <TDBilsborough (AT) Charter (DOT) net> wrote