|
Subject:
|
ADO excel2Excel....
|
|
Posted By:
|
XiaoAn
|
Post Date:
|
9/28/2006 10:05:33 AM
|
 Hi all,
I have a (probably very trivial) problem...I would like to ventilate data stored in a "bulk data" workbook to diverse final users workbooks using ADO and steering the whole process from another workbook. Opening and reading the first data set is no problem, but how do I download to the target workbooks? (ie how does one use the method .CopyFromRecordSet to an non opened data workbook?)
Sub StatIntRead()
Dim objConnect As ADODB.Connection
Dim szConnect As String
Dim szSQL As String
Dim myWkb As String
Dim rsData As ADODB.Recordset
Dim myUsr As String
myWkb = "Z:\Working\bulk.xls"
'Create connection chain
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & myWkb & ";" & _
"Extended Properties=Excel 8.0;"
myUsr="AAA" 'will loop on users
szSQL = "SELECT * FROM [Sheet1$A4:W65000] WHERE Usr = " & myUsr
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not rsData.EOF Then
' Feuil4.Range("a2").CopyFromRecordset rsData '<= this work OK
' but outputs in the active, open workbook
' I want to output in workbook AAA.xls
'
' If myUsr='AAA' Then ???? create a new connection?
'
Else
End If
'Clear up RecordSet
rsData.Close
Set rsData = Nothing
End Sub
|
|
