Write to Closed Workbook

Skip to first unread message

Chuck Adams

Jan 2, 2002, 12:24:57 PM1/2/02
Is there any way to write to a closed workbook from an active workbook using
VBA. Also, is there a way to lock this closed workbook while the procedure
is being run. Thanks.

Chuck Adams

Rob Bovey

Jan 2, 2002, 3:23:57 PM1/2/02
Hi Chuck,

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

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.
Set objConn = Nothing
End Sub

You'll need to modify the connection string Data Source to point to your
Excel file and modify the SQL statement to fit your data.

Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Chuck Adams" <ada...@mediaone.net> wrote in message

Reply all
Reply to author
0 new messages