ADO/Excel

Hi There,
I have a recordset that has data from multiple tables, I need to write this data
to an excel spread sheet using ADO, I know how to select from an Excel spread sheet, but is stuck up on this factor that I need to insert into the Excel Spread Sheet, pls give me a clue as to how to do it.


Dim cn as ADODB.Connection
set cn = new ADODB.Connection
'to Open the Connection to the Specified Excel File
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With



The above code I use to connect to Excel, Now I need to open the recordset and write to the Excel Spread Sheet.
Pls help.
Thanks in advance
Dipu Thomas

well the only way i can think of is to create a query then make a report from the query and export the report to excel....
hope this gets you in the right direction

midnightservice

You can use the Excel object library for that. You will need to add a refference ot it using the project>refferences menu.

Following code will select the field 'somefield' of the table 'sometable', and add it to column A

set XL = new Excel.Application

XL.Visible = true
set WB = XL.Workbooks.Add()
set WS = WB.Worksheets.Add()

Dim RST as ADODB.recordset
set RST = cn.execute("SELECT SomeField From SomeTable")

RST.MoveFirst
Dim rc as Long
rc = 0
Do Until RST.EOF
rc = rc + 1
WS.Range("A" & rc).Value = RST("somefield")
RST.MoveNext
Loop




Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

why are you using 4.0? use 5.0 also. it gives better functionality and compatibility