SQL loop of input values

[Tony Zappal]

Hi all,
I'm trying to put together a macro that will retrive data from an Oracle
database.
I have the databse connection section setup and working.
However the part I'm struggling with is the excel data to put into the query.

The scenerio is thus: I have in column A a variable range of values. That
range can be 10 numbers or 500 numbers or more.
What i'm trying to do(or think i need to do), is put those cells into an
array.
I however, need to limit the number of cells that go into the array to a max
of 50 at a time, due to the nature of the query.(also need to figure out how
to put commas between the numbers for the where clause in the query).

The query will then run using the array in the where clause of the script,
and output a single retrieved column to column B, and append to the end each
time the macro goes through the array values.

So i need to put that into a loop, which will stop when the range of values
finishes (i.e. via the next cell being empty).

Does that make sense?

If i could get some help with this it would be great.

Cheers,
TZ.

 

[Matthew Herbert]

Hi all,
I'm trying to put together a macro that will retrive data from an Oracle
database.
I have the databse connection section setup and working.
However the part I'm struggling with is the excel data to put into the query.

The scenerio is thus: I have in column A a variable range of values. That
range can be 10 numbers or 500 numbers or more.
What i'm trying to do(or think i need to do), is put those cells into an
array.
I however, need to limit the number of cells that go into the array to a max
of 50 at a time, due to the nature of the query.(also need to figure out how
to put commas between the numbers for the where clause in the query).

The query will then run using the array in the where clause of the script,
and output a single retrieved column to column B, and append to the end each
time the macro goes through the array values.

So i need to put that into a loop, which will stop when the range of values
finishes (i.e. via the next cell being empty).

Does that make sense?

If i could get some help with this it would be great.

Cheers,
TZ.

TZ,

The code below includes comments, including assumptions (be sure to
read these). This should get you close to what you are looking for,
but you should test this code first prior to implementing the query to
make sure it gives you your desired result. (You can uncomment the
"Debug.Print strQuery" lines and then watch the Immediate Window (i.e.
View | Immediate Window) to see how the string is being built).

Best,

Matthew Herbert

Sub BuildAndExecuteQuery()
Dim rngCell As Range
Dim rngList As Range
Dim intMax As Integer
Dim intCnt As Integer
Dim intQueryCnt As Integer
Dim strQuery As String
Dim blnRemainder As Boolean

'Assuming the list starts in A1 and the list is contiguous
Set rngList = Range("A1")
Set rngList = Range(rngList, rngList.End(xlDown))

'set the max number to append
intMax = 50

'check if there is a remainder; if the remainder exists
' then flag it
If rngList.Rows.Count Mod intMax = 0 Then
intQueryCnt = rngList.Rows.Count \ intMax
Else
intQueryCnt = rngList.Rows.Count \ intMax + 1
blnRemainder = True
End If

For Each rngCell In rngList.Cells

'counter to test against intMax
intCnt = intCnt + 1

'build the query string
strQuery = strQuery & rngCell.Value & ","

If intCnt = intMax Then

'remove the trailing ","
strQuery = Left(strQuery, Len(strQuery) - 1)
'Debug.Print strQuery

'Execute Query here (create a separate sub to do this
' wherein you pass strQuery)

'Output the results in the next Empty cell (create a
' separate sub to do this wherein you pass the output
' range) [Maybe something like the following:
' Range("B" & rows.Count).End(xlUp).Offset(1,0); however,
' this assumes the query won't return an empty cell/cells
' with the last entries]

'reset the counter and the string
intCnt = 0
strQuery = ""
End If
Next rngCell

'catch the remainder string for the query
If blnRemainder Then

'remove the trailing ","
strQuery = Left(strQuery, Len(strQuery) - 1)
'Debug.Print strQuery

'Execute Query here (call your sub)

'Ouput the results here (call your sub)
End If

End Sub

 

[Tony Zappal]

Thank you Matther. Your help is really appreciated. We managed to get it all
working with your help.
Cheers,
TZ.

Hi all,
I'm trying to put together a macro that will retrive data from an Oracle
database.
I have the databse connection section setup and working.
However the part I'm struggling with is the excel data to put into the query.

The scenerio is thus: I have in column A a variable range of values. That
range can be 10 numbers or 500 numbers or more.
What i'm trying to do(or think i need to do), is put those cells into an
array.
I however, need to limit the number of cells that go into the array to a max
of 50 at a time, due to the nature of the query.(also need to figure out how
to put commas between the numbers for the where clause in the query).

The query will then run using the array in the where clause of the script,
and output a single retrieved column to column B, and append to the end each
time the macro goes through the array values.

So i need to put that into a loop, which will stop when the range of values
finishes (i.e. via the next cell being empty).

Does that make sense?

If i could get some help with this it would be great.

Cheers,
TZ.

TZ,

The code below includes comments, including assumptions (be sure to
read these). This should get you close to what you are looking for,
but you should test this code first prior to implementing the query to
make sure it gives you your desired result. (You can uncomment the
"Debug.Print strQuery" lines and then watch the Immediate Window (i.e.
View | Immediate Window) to see how the string is being built).

Best,

Matthew Herbert

Sub BuildAndExecuteQuery()
Dim rngCell As Range
Dim rngList As Range
Dim intMax As Integer
Dim intCnt As Integer
Dim intQueryCnt As Integer
Dim strQuery As String
Dim blnRemainder As Boolean

'Assuming the list starts in A1 and the list is contiguous
Set rngList = Range("A1")
Set rngList = Range(rngList, rngList.End(xlDown))

'set the max number to append
intMax = 50

'check if there is a remainder; if the remainder exists
' then flag it
If rngList.Rows.Count Mod intMax = 0 Then
intQueryCnt = rngList.Rows.Count \ intMax
Else
intQueryCnt = rngList.Rows.Count \ intMax + 1
blnRemainder = True
End If

For Each rngCell In rngList.Cells

'counter to test against intMax
intCnt = intCnt + 1

'build the query string
strQuery = strQuery & rngCell.Value & ","

If intCnt = intMax Then

'remove the trailing ","
strQuery = Left(strQuery, Len(strQuery) - 1)
'Debug.Print strQuery

'Execute Query here (create a separate sub to do this
' wherein you pass strQuery)

'Output the results in the next Empty cell (create a
' separate sub to do this wherein you pass the output
' range) [Maybe something like the following:
' Range("B" & rows.Count).End(xlUp).Offset(1,0); however,
' this assumes the query won't return an empty cell/cells
' with the last entries]

'reset the counter and the string
intCnt = 0
strQuery = ""
End If
Next rngCell

'catch the remainder string for the query
If blnRemainder Then

'remove the trailing ","
strQuery = Left(strQuery, Len(strQuery) - 1)
'Debug.Print strQuery

'Execute Query here (call your sub)

'Ouput the results here (call your sub)
End If

End Sub