Excel Hints

Results 1 to 2 of 2

Thread: Solved: Loop to create repeating SQL statment with incrementing variables

  1. #1

    Solved: Loop to create repeating SQL statment with incrementing variables

    I have a list of ID numbers in an excel sheet and those numbers will be the fields in the SQL SELECT statment like this:

    SELECT [IDnumber1] from mytable where Field=[Variable] and Field1=[Variable1]

    I'm trying to get VBA to run the SELECT statement, write the results to Excel then Run it again and move to the next variable in the list so the second time thru would be:

    SELECT [IDnumber2] from mytable where Field=[Variable] and Field2=[Variable2]

    Keep going until it goes all the way through the list of numbers. Some of the variables in the Select dont change and others do. The length of the list varies so I planning to have a COUNTA return the total items in the list then use that result to form a For i = 1 to [COUNTA result]

    All this is due to a poor database design and the use of many VBA reserved words in the database fields. (Not my design!)

    Here is what I have so far..I put several breaks in the SQL statment so the variables would be on a new line by themselves..




    VB:
    Sub RolloverSimple() 
         
         'Declare variables'
        Dim objMyConn As ADODB.Connection 
        Dim objMyCmd As ADODB.Command 
        Dim objMyRecordset As ADODB.Recordset 
        Dim i As Integer 
        Dim c As Range 
         
        Set objMyConn = New ADODB.Connection 
        Set objMyCmd = New ADODB.Command 
        Set objMyRecordset = New ADODB.Recordset 
         
         'Open Connection'
        objMyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\RealogicTools\TestDB_v14.mdb" 
        objMyConn.Open 
         
         'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn 
         
         'Clear Contents
        Sheets("Sheet1").Select 
        ActiveSheet.Range("A4:ab9000").ClearContents 
         
         
         '--BEGIN LOOP-------- each range to increment until the end of the list
         ' For Each c In Range("a1:c2")
         
        objMyCmd.CommandText = "" 
        objMyCmd.CommandText = "SELECT  OP1.Name,  OP1.P" & _ 
        "" & Range("d1") & " " & _ 
        "FROM OAOutput AS OP1 " & vbCrLf 
        objMyCmd.CommandText = objMyCmd.CommandText & "WHERE (((OP1.[Section])=""Base Rent"" Or (OP1.[Section])=""Miscellaneous"") AND " & _ 
        "((OP1.LineID)=" & _ 
        "" & Range("c1") & "" & _ 
        ") AND ((OP1.PropID)=" & _ 
        """" & Range("StaticVar1") & """" & _ 
        ") AND ((OP1.VersionID)=" & _ 
        "" & Range("StaticVar2") & "" & _ 
        "));" 
         
         ' Debug.Print objMyCmd.CommandText
        objMyCmd.CommandType = adCmdText 
         
         'Open Recordset'
        Set objMyRecordset.Source = objMyCmd 
        objMyRecordset.Open 
         
         'Copy Data to Excel increment by 10 each time'
        ActiveSheet.Range("a5").CopyFromRecordset objMyRecordset 
         
        For i = 1 To objMyRecordset.Fields.Count 
            ActiveSheet.Cells(4, i).Value = objMyRecordset.Fields(i - 1).Name 
        Next i 
         'Next c
         '--------------END LOOP--------------
         
    End Sub 
    
    
    Formatting tags added by mark007

  2. #2
    Couldnt figure out how to edit my post...

    Still working on this...I think it needs to be done witha nested loop, the outer loop builds the sql statment while the inner loop updates the variables in the sql statment. I updated the code by breaking the SQL statment into several pieces so that the strings containing the variables can be separated and updated.

    VB:
    Sub RolloverSimple() 
         
         'Declare variables'
        Dim objMyConn As ADODB.Connection 
        Dim objMyCmd As ADODB.Command 
        Dim objMyRecordset As ADODB.Recordset 
        Dim i As Integer 
        Dim c As Range 
         
        Dim SQL1 As String 
        Dim SQL2 As String 
        Dim SQL3 As String 
        Dim SQL4 As String 
        Dim SQL5 As String 
        Dim SQL6 As String 
        Dim SQL7 As String 
        Dim SQL8 As String 
        Dim SQL9 As String 
        Dim SQL10 As String 
        Dim SQL11 As String 
         
        Set objMyConn = New ADODB.Connection 
        Set objMyCmd = New ADODB.Command 
        Set objMyRecordset = New ADODB.Recordset 
         
         'Open Connection'
        objMyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\RealogicTools\TestDB_v14.mdb" 
        objMyConn.Open 
         
         'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn 
         
         'Clear Contents
        Sheets("Sheet1").Select 
        ActiveSheet.Range("A4:ab9000").ClearContents 
         
         
         '--BEGIN OUTER LOOP-------- This section repeats until the end of the list of IDnumbers
         ' For Each c In Range("a1:c2")
         
        SQL1 = "SELECT OP1.LineID, OP1.Name, OP1.[Section], OP1.P" 
        SQL2 = "" & Range("d1") & " " 
        SQL3 = "FROM OAOutput AS OP1 " & vbCrLf 
        SQL4 = "WHERE (((OP1.[Section])=""Base Rent"" Or (OP1.[Section])=""Miscellaneous"") AND " 
        SQL5 = "((OP1.LineID)=" 
        SQL6 = "" & Range("c1") & "" 
        SQL7 = ") AND ((OP1.PropID)=" 
        SQL8 = """" & Range("StaticVar1") & """" 
        SQL9 = ") AND ((OP1.VersionID)=" 
        SQL10 = "" & Range("StaticVar2") & "" 
        SQL11 = "));" 
         
         
        objMyCmd.CommandText = "" 
        objMyCmd.CommandText = SQL1 & SQL2 & SQL3 & SQL4 & SQL5 & SQL6 & SQL7 & SQL8 & SQL9 & SQL10 & SQL11 
         
         ' SQL2 and   SQL 6 need to be updated on the inner loop
         
        Debug.Print objMyCmd.CommandText 
        objMyCmd.CommandType = adCmdText 
         
         'Open Recordset'
        Set objMyRecordset.Source = objMyCmd 
        objMyRecordset.Open 
         
         'Copy Data to Excel increment by 10 each time'
        ActiveSheet.Range("a5").CopyFromRecordset objMyRecordset 
         
         
         'Next c
         '--------------END OUTER LOOP--------------
         
    End Sub 
    
    
    Formatting tags added by mark007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •