Write to closed workbook code

[Todd Huttenstine]

I was doing a search in Google Groups for code that will write data to a
closed workbook and found this. I am trying to write data to cell A1 of
C:\test.xls. I do not know how to really use databases so this code is very
hard for me. Can anyone please tell me what I need to do to get this to
write lets say "test" to cell A1 of C:\test.xls? This code fails when it
gets to the line: objConn.Execute szSQL, , adCmdText Or
adExecuteNoRecords. I get error Run Time error '-2147217900 (80040e14)':
Automation error. Any help is greatly appreciated.


Thank you

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('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

Todd Huttenstine

 

[Tom Ogilvy]

What does your szSQL string look like?

does it look like the one you posted:

szSQL = "INSERT INTO <BookLevelName> " & _
"VALUES('Val1', 'Val2', Val3, Val4);"

If so, you need to define a name and put it in there.

--
Regards,
Tom Ogilvy

 

[Todd Huttenstine]

What do you mean? I do not know what BookLevelName means.

Can you please explain?

 

[Tom Ogilvy]

Insert => Name => Define

Name: MyName
Refers to: =Sheet1!$A$1

 

[onedaywhen]

Worth pointing out that it doesn't have to be a book level name e.g.

INSERT INTO [BookLevelName] (MyCol1, MyCol2) VALUES (1,2)

It can also be a sheet level name e.g.

INSERT INTO [Sheet1$SheetLevelName] (MyCol1, MyCol2) VALUES (1,2)

or a worksheet name e.g.

INSERT INTO [Sheet1$] (MyCol1, MyCol2) VALUES (1,2)

or a range address e.g.

INSERT INTO [Sheet1$A1:B999] (MyCol1, MyCol2) VALUES (1,2)

--