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.
'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
'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
' 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
ActiveSheet.Range("P1").Offset(1, 0).CopyFromRecordset rs
MsgBox "Data has been imported.", vbOKOnly