Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
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
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this? Any suggestions?
David
Hi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> 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
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this? Any suggestions?
> David
>