Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL loop of input values
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL loop of input values
On Aug 2, 7:06*pm, Tony Zappal
wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL loop of input values
Thank you Matther. Your help is really appreciated. We managed to get it all
working with your help. Cheers, TZ. "Matthew Herbert" wrote: On Aug 2, 7:06 pm, Tony Zappal wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent help with input box & loop | Excel Programming | |||
Input box with a loop | Excel Programming | |||
endless loop - using input box | Excel Programming | |||
Loop through a range, allow user input during... | Excel Programming | |||
Collecting textbox input within a for loop | Excel Programming |