Macro for running the SQL code multiple times

**sawoodalam1989** · 02-27-2013, 11:34 AM

The below SQL which is in the code will get the value from Sheet2.Range("C2") field and will execute the query.
My requirement is i have values in Sheet2.C3, Sheet2.C4, Sheet2.C5 and it goes on till there is a Blank Cell.
I would like the SQL to pick up the value in Sheet2.C3, Sheet2.C4 one by one and execute the query (It should stop once it finds a NULL value).

SQL:
select DT_KEY from dt_dim where CAL_DT = TO_DATE('" & Sheet2.Range("C2").Value & "', 'MM/DD/YYYY')

Sheet2:
Image.jpg

Kindly help me in the same

Sub Ora_Connection()

Dim sourceCnn As ADODB.Connection
Dim sourceRst As ADODB.Recordset

Set sourceCnn = New ADODB.Connection
Set sourceRst = New ADODB.Recordset

strCon = "Provider=OraOLEDB.Oracle;User ID=;Password=;Data Source="
  
sourceCnn.Open (strCon)
sourceRst.ActiveConnection = sourceCnn


sourceRst.Source = "select DT_KEY from dt_dim where CAL_DT = TO_DATE('" & Sheet2.Range("C2").Value & "', 'MM/DD/YYYY')"
sourceRst.Open

Sheets("Sheet2").Select

   Cells(lastrow, 1).Select
   Do While Not sourceRst.EOF
    Selection.Offset(1, 1) = sourceRst.Fields("DT_KEY")
  Selection.Offset(1, 0).Select
                   sourceRst.MoveNext
          Loop
  sourceRst.Close


End Sub

**JBeaucaire** · 02-27-2013, 12:10 PM

1) Declare a Range variable to reference all the source cells, either as constants or formulas, whichever is in those cells, constants or formulas
2) Declare a Range variable you can use to cycle through range above one at a time
3) Start a loop and insert your 2nd range variable into the loop, thus inserting one value from 1st range each time the loop executes.

Dim MyStrings As Range, MyStr As Range

Set sourceCnn = New ADODB.Connection
Set sourceRst = New ADODB.Recordset
Set MyStrings = Sheet2.Range("C2:C" & Rows.Count).SpecialCells(xlConstants)   'or xlFormulas
strCon = "Provider=OraOLEDB.Oracle;User ID=;Password=;Data Source="

sourceCnn.Open (strCon)
sourceRst.ActiveConnection = sourceCnn

For Each MyStr In MyStrings
    sourceRst.Source = "select DT_KEY from dt_dim where CAL_DT = TO_DATE('" & MyStr & "', 'MM/DD/YYYY')"
.
.
.
.
Next MyStr

**sawoodalam1989** · 02-27-2013, 06:51 PM

@JBeaucaire - Thanks for the solution. But i am new to Macros so kindly provide me the full code for my requirement.

**JBeaucaire** · 02-27-2013, 08:35 PM

Not really. I don't use the extended functions you're using, so I'm showing you how to get a part of your code replaced by a loop where you insert a variable string in where before you had a static string.

The . . . part is where you will need to adapt your code, shouldn't be a lot of adaptation, to use the variable as I've demonstrated.

It's not clear just by reading it what your DO....LOOP is actually doing.