Problems with looping through SQL query criteria in Excel vba

Posted on 2010-01-04
Last Modified: 2012-05-08
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


            ActiveSheet.Name = x.Offset(0, -13).Value & " (" & miniLoop & ")"

            miniLoop = miniLoop + 1



        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



        End If

    Next ws

Next x



MsgBox "Data has been imported.", vbOKOnly

End Sub

Open in new window

Question by:kathy1234
    LVL 18

    Accepted Solution


    May be..

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


    Author Closing Comment

    Thank you for the fast response.  It's working!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now