Write to closed workbook code

  • Thread starter Todd Huttenstine
  • Start date

T

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
 
Ad

Advertisements

T

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
 
T

Todd Huttenstine

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

Can you please explain?
 
Ad

Advertisements

O

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)

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top