Insert calculated value into Excel

[WaveOut]

I need help getting a calculated number into excel. I use the following sql statement:

ADO_rs.Source = "select ct_off_time, least(2+(0.044117*CT_OFF_GEN),8)/count(*) as mydata2 calculate avg from b_NRatio"

'it returns the following (which is correct):
ct_off_time mydata2
-------------------- --------------------------------
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
11-AUG-03 07:37:09.6 6.18406
15-APR-03 22:05:55.7 1.95985
09-MAY-03 21:25:45.0 1.97574
24-JAN-03 03:59:27.7 8
25-SEP-02 11:34:25.6 1.99868
16-AUG-03 09:16:47.9 6.1095
18-AUG-02 21:34:39.2 6.42846
28-JUL-03 10:37:57.9 6.10288
16-AUG-03 10:57:57.8 6.07862
23-OCT-02 14:18:13.8 1.91265
16-JAN-03 03:39:21.0 8
--------------------------------
AVG 5.25773


' now I want to put the calculated avg into a cell in excel. How do I do this?

ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!??what goes here??

Any help is appreciated.

David

 

[donutman]

If you're looking for a quick and dirty way to get the job done one time, then just create a table and use an insert statement to populate the table. Then export the table to an excell spreadsheet.
A more sophisticated approach would be to create a linked server into excel.
-Karl

 

[WaveOut]

donutman,
Thanks for the reply. I'm not sure how to get the average into the table:

DECLARE LOCAL TEMPORARY TABLE MODULE.x(VALUE int);

select ct_off_time, least(2+(0.044117*CT_OFF_GEN),8)/count(*) as mydata2 calculate avg from b_NRatio

How can I store the avg value into the Module.x temp table?

David

 

[donutman]

Create a table as though it is permanent.
Insert into NewTable
Select ct_off_time, least(2+(0.044117*CT_OFF_GEN),8)/count(*) as mydata2 calculate avg from b_NRatio

Then do the export.
BTW, if you are having the recordset returned to a VB application via an ado Data control, then you'll need to create an excel object. You can probably do a search on "excel" in the vb forum.
-Karl