Use ADO to transfer data from open excel file to closed excel file

I have and open Excel file (Open.xls) that I would like to place a button on
that would place data from a cell on the open sheet into a cell on a closed
sheet (Closed.xls) I would also like to check for certain cells' values in
the open sheet and place certain text in the closed sheet based on this.

Here the naive code I have been trying to do this with. It could be all
wrong, I'm a novice:

Sub BodyOrder()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=J:\My
Documents\APS Bodies and Options\Pacific-APS Order Sheet.xls;" & "Extended
Properties=""Excel 8.0;"""
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM [Sheet1]"
With rs
.Fields("A2") = Range("D2")
If Range("D3") = 2 Then
.Fields("B2") = "Chevy"
Else
.Fields("B2") = "Ford"
End If

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

see
http://support.microsoft.com/default...b;en-us;319998

BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


JCanyoneer wrote :

> I have and open Excel file (Open.xls) that I would like to place a
> button on that would place data from a cell on the open sheet into a
> cell on a closed sheet (Closed.xls) I would also like to check for
> certain cells' values in the open sheet and place certain text in the
> closed sheet based on this.
>
> Here the naive code I have been trying to do this with. It could be
> all wrong, I'm a novice:
>
> Sub BodyOrder()
> Dim cn As ADODB.Connection, rs As ADODB.Recordset
> Set cn = New ADODB.Connection
> cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=J:\My
> Documents\APS Bodies and Options\Pacific-APS Order Sheet.xls;" &
> "Extended Properties=""Excel 8.0;"""
> ' open a recordset
> Set rs = New ADODB.Recordset
> rs.Open "SELECT * FROM [Sheet1]"
> With rs
> .Fields("A2") = Range("D2")
> If Range("D3") = 2 Then
> .Fields("B2") = "Chevy"
> Else
> .Fields("B2") = "Ford"
> End If
>
> rs.Close
> Set rs = Nothing
> cn.Close
> Set cn = Nothing
> End Sub