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

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

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