Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
put query result into variable
Depending on if you expect one or more rows your would do:
dim vVar vVar = rst.GetRows or vVar = rst.Fields(0) RBS "andy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a value from a variable result | Excel Discussion (Misc queries) | |||
Replace ^ in web query result | Excel Worksheet Functions | |||
Importing result from Access query to Excel but the result only c. | Excel Discussion (Misc queries) | |||
Formula Result Won't Query | Excel Discussion (Misc queries) | |||
Passing SQL Query Result Into A Variable | Excel Programming |