Retrieve Data from SQL Server

[AnilPullagura]

Hello Pros,

I have written a code to retrieve data from SQL Server to Excel but the performance is slow. The project goes like:

There are values in the Range A2:A, and for each of these values corresponding 3 values are to be retrieved for SQL DB. I did accomplish that, however the performance is slow because the code written by me will take value in A2, execute the query and retrieve data and dumps that record to B2,C2 and D2. Next the macro proceeds to A3 and performs the same routine. This will not consume time if the range is low(say 100 cells in Acolumn) but I now encountered that this macro will be used for 10000cells of data . I am happy for the success and at the same time lost in confusion.

Is there any method where all the values(A2:A) can be passed to the query and after retrieving the recordset, cant we populate them to Excel?
I am a novice in both SQL and VBA. Please check if my code below and let know the possibility of this.

[SIZE=3][FONT=Calibri]Sub enjoi()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.ScreenUpdating = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.DisplayAlerts = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim cnrptgprod As ADODB.Connection<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set cnrptgprod = New ADODB.Connection<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Sheets("Data").Select<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strConn As String<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]'Range("A7:V444").Clear<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strConn = "Provider=SQLOLEDB.1;"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strConn = strConn & "Data Source=rptgprod;INITIAL CATALOG=BpoReportData;"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strConn = strConn & " INTEGRATED SECURITY=sspi;"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]cnrptgprod.Open strConn<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim rsrptgprod As ADODB.Recordset<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim i As Integer<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rsrptgprod = New ADODB.Recordset<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]With rsrptgprod<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   .ActiveConnection = cnrptgprod<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   lastrw = Range("A1").End(xlDown).Row<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   For i = 2 To lastrw   <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   .Open "SELECT MAX(CDML_CUR_STS), MAX(CDML_SEQ_NO), D.WQDF_DESC FROM facippr0.CMC_CLCL_CLAIM A WITH(NOLOCK) INNER JOIN facippr0.CMC_CDML_CL_LINE B WITH(NOLOCK) ON A.CLCL_ID = B.CLCL_ID INNER JOIN facippr0.dbo.NWX_WMHS_MSG_HIST C WITH(NOLOCK)ON A.CLCL_ID = C.WMHS_MESSAGE_ID INNER JOIN facippr0.dbo.NWX_WQDF_QUEUE_DEF D WITH(NOLOCK) ON C.WQDF_QUEUE_ID = D.WQDF_QUEUE_ID WHERE C.WMHS_ROUTING_DTM = (SELECT MAX(WMHS_ROUTING_DTM) FROM  facippr0.dbo.NWX_WMHS_MSG_HIST WHERE WMHS_MESSAGE_ID = B.CLCL_ID) AND A.CLCL_ID = '" & Range("A" & i) & "'" & "GROUP BY D.WQDF_DESC"    <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Do Until rsrptgprod.EOF<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Sheets("Data").Cells(i, 2) = .Fields(0)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Sheets("Data").Cells(i, 3) = .Fields(1)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Sheets("Data").Cells(i, 4) = .Fields(2)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           .MoveNext[/FONT][/SIZE][SIZE=3][FONT=Calibri] <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Loop<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .Close<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Next i[/FONT][/SIZE][SIZE=3][FONT=Calibri]  <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End With<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]cnrptgprod.Close<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rsrptgprod = Nothing<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set cnrptgprod = Nothing<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.ScreenUpdating = True<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.DisplayAlerts = True<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>

Thanks Much,
Anil