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

G

Guest

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
 

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

Similar Threads

Exporting data with vba from Excel to an Access 2007 db with password 8
Overwrite a record instead of add 3
ADO data access to excel 2
Using ADODB in MS Excel. 4
Problem with .Update 4
ADO adStateOpen = 0 2
ADO Access Query bombs when data has apostrophe 1
import table access to excel 1

Top