ADO/Excel

[thomas_dipu]

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

[midnightservice]

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

[Cakkie]

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

[pythonpower]

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