Write to Closed Workbook

[Chuck Adams]

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]

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

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
http://www.appspro.com/

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

"Chuck Adams" <ada...@mediaone.net> wrote in message
news:JnHY7.13295$CS6.4...@typhoon.jacksonville.mediaone.net...