Tuesday, November 24, 2015

Select Query in For Loop using VBA

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rowcount As Long, emprng As Range
Dim mycoll As Collection, i As Integer
Dim myArray(), rowcounter As Integer, counter As Integer
Sub updateclaimedtrainingDays()
rowcount = ThisWorkbook.Sheets(1).Range("D" & Rows.Count).End(xlUp).Row
Set emprng = ThisWorkbook.Sheets(1).Range("D2:D" & rowcount)
Set mycoll = New Collection
On Error Resume Next
'startdate = CDate("10/27/2015")
'endDate = CDate("10/28/2015")
conn.ConnectionString = "Data Source=TADA;Initial Catalog=Firdb;uid=kshrNew;pwd=A343jNMS;"
conn.Open
For Each cell In emprng
    mycoll.Add cell, CStr(cell)
Next
counter = 0
For i = 1 To mycoll.Count
        Set rst = conn.Execute("Select t2.DaysQty from tblTADATransDetail t2 where t2.TAId IN( Select t1.TAId  from tblTADATransMaster  t1 where (t1.CreatedDate >= '" & startdate & "' and t1.CreatedDate <='" & endDate & "') And t1.EmpId='" & mycoll(i) & "')and t2.NameOfExpenditure='1. Training Incentives'")
        If Not rst.EOF Then
            'cell.Offset(0, 16).CopyFromRecordset rst.Fields(4).Value
            myArray = rst.GetRows()
            rowcounter = UBound(myArray, 2)
            For j = 0 To rowcounter
            counter = counter + 1
           
                ThisWorkbook.Sheets(1).Range("T" & (1 + counter)) = myArray(0, j)
            Next
        Else
        counter = counter + 1
            ThisWorkbook.Sheets(1).Range("T" & (1 + counter)) = 0
        End If
Next
 rst.Close
Set rst = Nothing
Set conn = Nothing

End Sub

No comments:

Post a Comment