Looping the String in SQL Statement

[aravindhan_3]

Hi,
I am not sure if this has to be posted under SQL FORUM, but i think its something to do with excel VBA so I am posting here.

I would appreciate verymuch if you could help me on this.

I have VBA code which runs a sql query and gives me an output.

what it does now:-
take the store number entered in A3 and run the SQL query(which is in Red) which gives me the output correctly. ( it pastes the output in sheet2)

I change the store number in A3 and run the macro155 times for different stores.

Public cn As ADODB.Connection
Public CWrs As ADODB.Recordset
Public LWrs As ADODB.Recordset
Public CmdSQLData As ADODB.Command
Dim myStr As String

Sub Where_Else()
    Sheet1.Activate
    Sheet1.Range("A3").Select
    Dim stSQL, retailDate2 As String
    Dim Store, Store2 As String
    Dim i, j As Integer
    Dim readColumn As Long
    Store = Range("A3").value    -- ' Store Number
    Store2 = "(" & Store & ")"
    Set cn = New ADODB.Connection
    Set CWrs = New ADODB.Recordset
    Set CmdSQLData = New ADODB.Command
    cn.Open "DSN=DW MASTER; Driver=Teradata; Server = tdpm; Database=DXWI_PROD_ROI_VIEW_ACCESS; UID=roi_general; PWD=roi; OPTION=3"
    Set CmdSQLData.ActiveConnection = cn 
    stSQL = "Select ron, store_name, spend,xrank " & _
             "From ( " & _
             "select ron, store_name, spend, rank(spend) As xrank " & _
             "From ( " & _
             "Select bsk.Retail_Outlet_Number As ron, store_name,Sum(sales_value) As spend " & _
             "From    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
             "On  cal.calendar_date = bsk.transaction_date " & _
             "Inner Join dxwi_prod_roi_view_access.vwi0rot_retail_outlet rot " & _
             "On  bsk.retail_outlet_number = rot.retail_outlet_number " & _
             "Where   household_number In " & _
             "(sel household_number " & _
             "From    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
             "On  cal.calendar_date = bsk.transaction_date " & _
             "where retail_outlet_number = " & "" & Store2 & "" & _
             "And year_week_number Between 201001 And 201026 Group   By 1) " & _
             "And year_week_number Between 201001 And 201026 " & _
             "And country_code = 7 " & _
             "And bsk.retail_outlet_number <> " & "" & Store2 & "" & _
             "And store_name Not Like '%pfs%' " & _
             "Group   By 1,2) x " & _
             ")A " & _
             "where xrank between 1 and 5 "
    CmdSQLData.CommandText = stSQL
    CmdSQLData.CommandType = adCmdText
    CmdSQLData.CommandTimeout = 0
    Set rs = CmdSQLData.Execute()
    Set rs = CmdSQLData.Execute()
    Sheet2.Activate
    For c = 0 To rs.Fields.Count - 1
        Sheet2.Cells(1, c + 1) = rs.Fields(c).Name
    Next c
    r = 2
    Do While Not rs.EOF
         For c = 0 To rs.Fields.Count - 1
             Sheet2.Cells(r, c + 1) = rs.Fields(c).value
         Next c
         r = r + 1
         rs.MoveNext
    Loop
    ' End If
    cn.Close
End Sub

Now what I need:-

Instead of taking one store number from Cell A3, I will enter all the store numbers one after the other from A3.

The code has to loop through all the store numbers in the query and pastes the output one after the other in sheet2.
that is, take the first store number and run the query, paste the output in sheet 2, and again run the query for 2nd store number, paste the output in sheet2 below the previous one.

Thanks for your help in advance
Cross post: http://www.mrexcel.com/forum/showthr...96#post2564496