Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move entire row of data from open to closed file by entering date | Excel Discussion (Misc queries) | |||
Is transfer data from one Excel file to another possible? | Excel Discussion (Misc queries) | |||
VBA - on a button event, open another closed file, post changes, close file | Excel Programming | |||
Need FASTEST way to get data from a large closed Excel File | Excel Programming | |||
Open delimited text file to excel without changing data in that file | Excel Programming |