Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weirdness
I have the following code which loops through values in the Column A and
uses the value from Column A in a Query that returns data from a database and inserts the returned data to columns C and D. (It also loops through all the sheets in the workbook) After 8 rows, the population becomes a column insertion. If I limit the loop to the first 8 rows, everything works fine. After 8 rows, I get new C and D columns for every iteration of my loop. Any help would be greatly appreciated. Thanks Sub GetInfo() Dim strSqlString As String Dim strConnString As String Dim shtSheet As Worksheet Dim lngX As Long Dim rngCell As Range Dim rngCol As Range Dim intLastRow As Integer For Each shtSheet In ActiveWorkbook.Sheets Select Case MsgBox("Get info for the items listed on the " & shtSheet.Name & " Sheet?", vbYesNoCancel, "Get Sheet Info?") Case vbYes shtSheet.Activate 'Determine Last Used Row and use for Selection Range intLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For lngX = 2 To intLastRow DoEvents If Trim(Range("B" & lngX).Text) < "" Then 'Query Database and return data to current row and column D strSqlString = "SELECT im.sales_pricing_unit as UOM, il.standard_cost * im.sales_pricing_unit_size as COMM FROM inv_mast as im (NOLOCK), inv_loc as il (NOLOCK) WHERE im.item_id = '" & Trim(Range("B" & lngX).Text) & "' AND il.inv_mast_uid = im.inv_mast_uid" strConnString = "ODBC;DSN=MyDSN;Database=MyDB" With shtSheet.QueryTables.Add(Connection:=strConnString , _ Destination:=Range("C" & lngX), Sql:=strSqlString) .BackgroundQuery = False .FieldNames = False .Refresh End With End If Next Case vbCancel Exit Sub End Select Next shtSheet End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weirdness
Hello again, Kevin: I am not sure since I don't know the details, but from
my experience Excel contains a "memory" effect when it comes to query tables and their associated ranges (columns). My guess is that rows 1-8 are in an existing querytable - so as long as you are in that range and you are using QueryTables.Add it recognizes/uses the current "column set"; but if you venture beyond that range it thinks it needs to create the space for some new columns. Just a guess based on my experience with the sometimes strange behavior of querytables. Rectifying it might be difficult, at least if you want to continue to use the same method for adding the data to your spreadsheet. You could get around it by not using querytables, unless you have a particular reason for wanting them (like refreshing the data periodically?) - in that case you could modify the sub to read the recordset results and using VBA code to copy them into the proper cells. "Kevin L" wrote: I have the following code which loops through values in the Column A and uses the value from Column A in a Query that returns data from a database and inserts the returned data to columns C and D. (It also loops through all the sheets in the workbook) After 8 rows, the population becomes a column insertion. If I limit the loop to the first 8 rows, everything works fine. After 8 rows, I get new C and D columns for every iteration of my loop. Any help would be greatly appreciated. Thanks Sub GetInfo() Dim strSqlString As String Dim strConnString As String Dim shtSheet As Worksheet Dim lngX As Long Dim rngCell As Range Dim rngCol As Range Dim intLastRow As Integer For Each shtSheet In ActiveWorkbook.Sheets Select Case MsgBox("Get info for the items listed on the " & shtSheet.Name & " Sheet?", vbYesNoCancel, "Get Sheet Info?") Case vbYes shtSheet.Activate 'Determine Last Used Row and use for Selection Range intLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For lngX = 2 To intLastRow DoEvents If Trim(Range("B" & lngX).Text) < "" Then 'Query Database and return data to current row and column D strSqlString = "SELECT im.sales_pricing_unit as UOM, il.standard_cost * im.sales_pricing_unit_size as COMM FROM inv_mast as im (NOLOCK), inv_loc as il (NOLOCK) WHERE im.item_id = '" & Trim(Range("B" & lngX).Text) & "' AND il.inv_mast_uid = im.inv_mast_uid" strConnString = "ODBC;DSN=MyDSN;Database=MyDB" With shtSheet.QueryTables.Add(Connection:=strConnString , _ Destination:=Range("C" & lngX), Sql:=strSqlString) .BackgroundQuery = False .FieldNames = False .Refresh End With End If Next Case vbCancel Exit Sub End Select Next shtSheet End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weirdness
Hi K,
Thanks for your other response also. I gave up on query tables and created the disconnected dataset as you suggested in your response to my other post. Everything is working well now. Thanks Again. "K Dales" wrote in message ... Hello again, Kevin: I am not sure since I don't know the details, but from my experience Excel contains a "memory" effect when it comes to query tables and their associated ranges (columns). My guess is that rows 1-8 are in an existing querytable - so as long as you are in that range and you are using QueryTables.Add it recognizes/uses the current "column set"; but if you venture beyond that range it thinks it needs to create the space for some new columns. Just a guess based on my experience with the sometimes strange behavior of querytables. Rectifying it might be difficult, at least if you want to continue to use the same method for adding the data to your spreadsheet. You could get around it by not using querytables, unless you have a particular reason for wanting them (like refreshing the data periodically?) - in that case you could modify the sub to read the recordset results and using VBA code to copy them into the proper cells. "Kevin L" wrote: I have the following code which loops through values in the Column A and uses the value from Column A in a Query that returns data from a database and inserts the returned data to columns C and D. (It also loops through all the sheets in the workbook) After 8 rows, the population becomes a column insertion. If I limit the loop to the first 8 rows, everything works fine. After 8 rows, I get new C and D columns for every iteration of my loop. Any help would be greatly appreciated. Thanks Sub GetInfo() Dim strSqlString As String Dim strConnString As String Dim shtSheet As Worksheet Dim lngX As Long Dim rngCell As Range Dim rngCol As Range Dim intLastRow As Integer For Each shtSheet In ActiveWorkbook.Sheets Select Case MsgBox("Get info for the items listed on the " & shtSheet.Name & " Sheet?", vbYesNoCancel, "Get Sheet Info?") Case vbYes shtSheet.Activate 'Determine Last Used Row and use for Selection Range intLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For lngX = 2 To intLastRow DoEvents If Trim(Range("B" & lngX).Text) < "" Then 'Query Database and return data to current row and column D strSqlString = "SELECT im.sales_pricing_unit as UOM, il.standard_cost * im.sales_pricing_unit_size as COMM FROM inv_mast as im (NOLOCK), inv_loc as il (NOLOCK) WHERE im.item_id = '" & Trim(Range("B" & lngX).Text) & "' AND il.inv_mast_uid = im.inv_mast_uid" strConnString = "ODBC;DSN=MyDSN;Database=MyDB" With shtSheet.QueryTables.Add(Connection:=strConnString , _ Destination:=Range("C" & lngX), Sql:=strSqlString) .BackgroundQuery = False .FieldNames = False .Refresh End With End If Next Case vbCancel Exit Sub End Select Next shtSheet End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrow key weirdness | Excel Discussion (Misc queries) | |||
Conditional Sum weirdness . . . | Excel Worksheet Functions | |||
VBA IDE Weirdness.... | Excel Programming | |||
Charting Weirdness and VBA | Excel Programming | |||
Charting Weirdness and VBA | Excel Programming |