CODEWITHSUNDEEP
sql-server-2008excelvba
HL8
HL8

Reputation: 1419

Excel VBA - Running SQL script multiple times with different variable value

I want to run a script in my macro multiple times by changing variable values. Below is an example of my code that I run for one value. The line of code I would like to change is 

sScript = sScript + "where m.outletid in ('" & sOutletId & "') " & vbCrLf

Sometime I want the where clause to be

where m.outletid in ('12314')

or

where m.chainid in ('411')...

Code:

Sub Report()
    Dim sScript As String
    Dim sServer As String
    Dim sDatabase As String
    Dim sTransTable As String
    Dim iVal As Integer
    Dim iReturnVal As Integer
    Dim SheetExists As Worksheet
    Dim WK_SHEET As String

       sServer = Trim(UserForm1.txtServer.Value)
       sDatabase = Trim(UserForm1.txtDatabase.Value)
       sTransTable = Trim(UserForm1.txtTransTable.Value)

       For Each SheetExists In Worksheets
          If SheetExists.Name = ("Report") Then
          Application.DisplayAlerts = False
          Sheets("Report").Delete
          Application.DisplayAlerts = True
      Exit For
      End If
      Next SheetExists

      Worksheets.Add after:=Sheets("Sheet1")
      ActiveSheet.Name = ("Report")

      WK_SHEET = "Report"

      Sheets(WK_SHEET).Select

       sOutletId = "12314"

       sScript = "Select top 10 m.CustNumber, m.Name, sum(t.Transvalue) " & vbCrLf        
       sScript = sScript + "from " & sTransTable & " t " & vbCrLf
       sScript = sScript + "where m.outletid in ('" & sOutletId & "') " & vbCrLf
       sScript = sScript + "Group by m.CustNumber, m.Name " & vbCrLf
       sScript = sScript + "order by sum(t.Transvalue)Desc " & vbCrLf

      iReply = MsgBox(Prompt:="Do you wish to continue with the following script for Top 10 Customers?" + sScript + "", _
    Buttons:=vbYesNo, Title:="Run MACRO Top 10 Reports")
    If iReply = vbNo Then
        End
    End If

    iVal = execute_sql_select(WK_SHEET, 2, 1, sServer, sDatabase, sScript)

    Sheets(WK_SHEET).Name = "Outlet" & sOutletId & "Top 10 by Spend"

Now I would like to re run the above with OutletId 12315...how can I do this? Do I use some sort of loop?

Upvotes: 0

Views: 733

Answers (1)

Thit Lwin Oo
Thit Lwin Oo

Reputation: 3448

You can keep list of OutletId into Array. Then get each OutletId from Array (for loop) and execute your sql script.

Pseudu code

Array listOutid = new Array[12,13,14,15];

for(int idx = 0; idx < listOutid.Length; idx++)
{
    var OutletId = listOutid[idx];

    //put ur sql statement and execute here..
}

Upvotes: 1

Related Questions