loop through range + call query

[peach255]

I have a macro that runs a query from an external db. In
the WHERE clause, it will be taking the parameter values
from 2 cells in the sheet. This is what I have ...

Sub Plate()
plateid = Worksheets("sheet1&quot.Range("A2&quot.Value
wellref = Worksheets("sheet1&quot.Range("B2&quot.Value
With ActiveSheet.QueryTables.Add(Connection:=Array(Array(
.........
"SELECT ... FROM ... WHERE (OLPTID='" & plateid
& "') AND (PTODWELLREFERENCE='" & wellref & "')" _
)

However, instead, of only allowing the users to enter only
one set of parameters, I would like the user to enter more
than one set ... for ex., user enters parameter values for
A2 & B2, and then another parameter values in A3 & B3, etc
etc. (unlimited). How can I modify my code to loop through the range starting from A2 & B2 and plug in the values to the Where clause to query the db and return the Select values, and then then loop to the next cells A3 & B3?
Thank you!

 

[xlbo]

What I'd do is loop thru the cells and build a comma seperated string

eg
'Get Number of options
myNum = range("A65536&quot.end(xlup).row - 1
'This assumes that your list of options starts in A2
for i = 1 to myNum
if i = 1 then
myOptions = Range("A" & i).text
else
myOptions = myOptions & "," & Range("A" & i).text
end if
next i


Then use the IN statement in SQL
"WHERE OLPTID IN '" & myOptions & "'"

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[xlbo]

ooops - forgot the brackets:
"WHERE OLPTID IN ('" & myOptions & "')"

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[SkipVought]

Hi,

Here's a way, assuming that the data in columns 1 & 2 is both ISOLATED and CONTIGUOUS and has headings plateid & wellref ...

Sub ConstructQuery()
    Dim sQuery As String, sValues As String, r As Range
    Cells(1, 1).CurrentRegion.CreateNames _
       Top:=True, _
       Left:=False, _
       Bottom:=False, _
       Right:=False
    sValues = ""
    
    For Each r In [plateid]
       If sValues = "" Then
          sValues = "(OLPTID='" & r.Value & "') AND (PTODWELLREFERENCE='" & r.Offset(0, 1).Value & "') "
        Else
          sValues = sValues & "AND (OLPTID='" & r.Value & "') AND (PTODWELLREFERENCE='" & r.Offset(0, 1).Value & "') "
       End If
    Next
    sQuery = "SELECT ... FROM ... WHERE " & sValues
    Cells(1, 4).Value = sQuery
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com