• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

Problems with looping through SQL query criteria in Excel vba

I'm using Excel 2003 and using vba to run a SQL query against our AS/400.  I loop through some values in a range on a worksheet and create (copy) worksheets based on the values in the range.  My problem is that I don't know how to loop through the criteria in my query at the same time.  

The routine loops through the values in column P, finds the worksheet (hidden) that matches it, makes a new copy and names the new worksheet with the value in column B.
      B                    P      Q                      R
13  First One      FO      field1 criteria      field2 criteria
14  Next One      SO      field1 criteria      field2 criteria
15  Third One      FO      field1 criteria      field2 criteria
16  Last One      FO      field1 criteria      field2 criteria

So I end up with sheets "First One", "Next One", etc.  Each sheet layout is based on column P and the name is based on column B.  So far so good.

After the new sheet is created, a SQL query is run against the AS/400 and the recordset is pasted into the new sheet.  So the data on "First One" is for that name, the data on "Next One" is for that name, etc.

My problem is that I can get it to work for "First One" if I specify the values for field1 and field2 as Q13 and R13, respectively.  But then I get the same data on all the sheets.  I don't know how to loop through the columns Q and R at the same time I'm looping through P.

Here's what I'd like to happen:

1.  See "FO" in P13, find the hidden sheet called "FO", copy it and name it "First One" based on column B.
2.  Run the SQL query using Q13 and R13 as my criteria.
3.  Start the next loop
4.  See "SO" in P14, find the hidden sheet called "SO", copy it and name it "Next One" based on column B.
5.  Run the SQL query using Q14 and R14 as my criteria.
6.  Keep looping until there aren't anymore values in the range P13:P22.

I'm including my vba code below.  I appreciate any help you can give.  Thanks.

Sub GetInfo()

'These variables are used getting data from AS/400
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

'This variable is used for the sql query (to split long lines)
Dim sSQL As String
 
'Begin import from AS/400
con.Open "PROVIDER=IBMDA400;Data Source=XXXXXXXXX;USER ID=" & Sheets("Input").Range("D9") & ";PASSWORD=" & Sheets("Input").Range("D10") & ";"
Set rs.ActiveConnection = con

'Loop thru marked cells, create tab, run SQL against AS/400
Dim x As Range

For Each x In Sheets("Input").Range("P13:P22")
 
    Dim ws As Worksheet
 
    For Each ws In ActiveWorkbook.Worksheets

'if the worksheet name equals the current value of x (the value shown in P13:P22) copy the worksheet and name it the current value of x 
        If ws.Name = x Then
            ws.Copy Before:=Worksheets(ws.Name)

'renames the worksheet with the value in column B.  if a worksheet already exists with that name, add a number to the name so sheet "First One" becomes "First One (1)"
        On Error Resume Next
        ActiveSheet.Name = x.Offset(0, -13).Value & "  "
        If Err.Number <> 0 Then
            Dim miniLoop As Integer
            miniLoop = 1
            Do While Err.Number <> 0
            Err.Clear
            ActiveSheet.Name = x.Offset(0, -13).Value & " (" & miniLoop & ")"
            miniLoop = miniLoop + 1
            Loop
            Err.Clear
        End If
   
' At this point the worksheet has been created and named to match the value on sheet "Input"

' Now the query is run against AS/400
         
        sSQL = "SELECT field1,field2,field3,field4,field5,field6,field7,field8 " & vbCrLf
        sSQL = sSQL & "FROM Library.Table " & vbCrLf

'The following line is the problem 
        sSQL = sSQL & "WHERE (field1=" & Sheets("Input").Range("Q13") & " AND field2=" & Sheets("Input").Range("R13") & ")" & vbCrLf

'We're good from this point on
        rs.Open (sSQL)
        ActiveSheet.Range("P1").Offset(1, 0).CopyFromRecordset rs
        rs.Close
        
        End If
    Next ws
Next x

Sheets("Input").Select
Range("A1").Select

MsgBox "Data has been imported.", vbOKOnly

End Sub

Open in new window

0
kathy1234
Asked:
kathy1234
1 Solution
 
krishnakrkcCommented:
Hi,

May be..

sSQL = sSQL & "WHERE (field1=" & x.Offset(, 1) & " AND field2=" & x.Offset(, 2) & ")" & vbCrLf

0
 
kathy1234Author Commented:
Thank you for the fast response.  It's working!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now