Weirdness

[Kevin L]

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

 

[K Dales]

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]

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.