put query result into variable

hi hope someone can help
i can connect to oracle through excel no problem
the problem i am having is i want to put the result of the following query
into a varable
at the moment i return the result into a cell on a excel worksheet then use
the value in the cell for a second query
here is the query i use

Sub form_a()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
Dim col As Integer
Dim data_to_find As Variant
Dim cttab As String

cnn.Open "Driver={microsoft odbc for oracle};" & _
"Server=EASY ;" & _
"Uid=easy;" & _
"Pwd=easy;"




sql = " select distinct code"
sql = sql & " from history"
sql = sql & " where trunc(date_time) = trunc(sysdate-1)"
rst.Open sql, cnn, adOpenKeyset, adLockReadOnly
Cells(1, 1).Select
ActiveCell.CopyFromRecordset rst
cttab = "CT_" & Cells(1, 1).Value

rst.Close

sql = " select brand from " & cttab & " "
sql = sql & " where trunc(calldate) = trunc(sysdate-1)"
sql = sql & " and brand is not null"
rst.Open sql, cnn, adOpenKeyset, adLockReadOnly


Cells(1, 1).Select
ActiveCell.CopyFromRecordset rst
rst.Close


End Sub

Depending on if you expect one or more rows your would do:

dim vVar

vVar = rst.GetRows

or

vVar = rst.Fields(0)


RBS


"andy" <[email protected]> wrote in message
news:[email protected]...
> hi hope someone can help
> i can connect to oracle through excel no problem
> the problem i am having is i want to put the result of the following query
> into a varable
> at the moment i return the result into a cell on a excel worksheet then
> use the value in the cell for a second query
> here is the query i use
>
> Sub form_a()
> Dim cnn As New ADODB.Connection
> Dim rst As New ADODB.Recordset
> Dim sql As String
> Dim col As Integer
> Dim data_to_find As Variant
> Dim cttab As String
>
> cnn.Open "Driver={microsoft odbc for oracle};" & _
> "Server=EASY ;" & _
> "Uid=easy;" & _
> "Pwd=easy;"
>
>
>
>
> sql = " select distinct code"
> sql = sql & " from history"
> sql = sql & " where trunc(date_time) = trunc(sysdate-1)"
> rst.Open sql, cnn, adOpenKeyset, adLockReadOnly
> Cells(1, 1).Select
> ActiveCell.CopyFromRecordset rst
> cttab = "CT_" & Cells(1, 1).Value
>
> rst.Close
>
> sql = " select brand from " & cttab & " "
> sql = sql & " where trunc(calldate) = trunc(sysdate-1)"
> sql = sql & " and brand is not null"
> rst.Open sql, cnn, adOpenKeyset, adLockReadOnly
>
>
> Cells(1, 1).Select
> ActiveCell.CopyFromRecordset rst
> rst.Close
>
>
> End Sub
>
>
>