ADO SQL Code Problems (Continued from earlier post)

[Todd Huttenstine]

The below code is what I found in Google groups. Here is the link to the
post below.
http://groups.google.com/groups?q=w...TF-8&selm=eXlMFu8kBHA.2168@tkmsftngp04&rnum=5

I have tried for days to get this to work. I have even sat my source
spreadsheet in ODBC administrator as a source. I also added the reference
as described below. I have no idea what I am doing wrong. Can anyone
please help?

My source spreadsheet I am trying to add a line to is C:\TEST.XLS. It has 2
columns A and B. ColumnA header is AAA and ColumnB is BBB. I simply want
to have this code in another spreadhseet and have it add a record to the
file.

If you have tabular data you can write to a closed workbook using ADO.
Here's an example procedure. Note that you'll need to add a reference to the
Microsoft ActiveX Data Objects 2.x library from your project in order to run
this.

Public Sub WorksheetInsert()
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:\Files\Sales.xls;" & _
"Extended Properties=Excel 8.0;"
' Create the SQL statement.
szSQL = "INSERT INTO <BookLevelName> " & _
"VALUES('Val1', 'Val2', Val3, Val4);"
' 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
End Sub


Heres my code that I modified from above.

Public Sub WorksheetInsert()
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 <BookLevelName> " & _
"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
End Sub


Does anyone know what I am doing wrong?

 

[Chris2]

Todd Huttenstine,

Well, without knowing the errors that are occuring, it's difficult to know
what is being done wrong.

However, I spot something of interest in the example code.

szSQL = "INSERT INTO <BookLevelName> " & _
"VALUES('TestVal1', 'TestVal2');"

<BookLevelName> must be replaced, I believe by the name of the
spreadsheet.
I think the format will resemble either:

1) [pathname\filename].worksheetname

2) worksheetname

I repeat, I *think* it will resemble that, you'll want to do some research
on how to name a spreadsheet in place of a tablename.

Note: The groups microsoft.public.access.modulesdaovba.ado or
microsoft.public.excel.programming may know more.


Sincerely,

Chris O.