Sleeper: Querytables.add with multiple request (range get confused)

[Scrap]

Hello, here is my problem:
it seems like the range("A + the variable" get confused when datas are add on the sheet.
The visual result is in the attachment.


Do Until Cells(R, 5) = "TOTAL:"
vide:
If Cells(R, 4) = "" Then
R = R + 1
GoTo vide
End If
carte = cells(R, 4)
sqlstring = "select sum(temp) from BCV where Carte= '" & Carte & "'
connstring = "ODBC;DSN=DEV_DCI;UID=sa;PWD=abcde;Database=DE V_DCI
With ActiveSheet.QueryTables.Add(Connection:=connstring , Destination:=
_Range("A" & R & "), Sql:=sqlstring)
.Refresh
End With
R = R + 1
Loop


Thank you.

[Norie]

Why do you have this?


Range("A" & R & ")

It should just be


Range("A" & R)

[Scrap]

OK with or without it, it is the same result.

I just figured out something: If the first loop is done once, the data is correctly place under in the same row.

[Scrap]

:thumb If you are using querytables.add in a loop, you may face this problem:
-When the datas are inserted in the next row, a columns is inserted.

To solve the problem:
-Make your code look like this:


Do Until Cells(R, 5) = "TOTAL:"
Do Until (IsEmpty(Cells(R, 4)) = False)
R = R + 1
Loop
Carte = Cells(R, 4)
sqlstring = "select sum(Temps_Arrondi) from Presence_BCV_App where Carte= '" & Carte & "' and ([Date] > '" & FrstDay & "' and [Date] < '" & LastDay & "') "
connstring = "ODBC;DSN=DEV_DCI;UID=sa;PWD=serveurdci;Database=DEV_DCI"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Cells(R, 11), Sql:=sqlstring)
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery = False
End With
R = R + 1
Loop

The italic part may be the cause if non-existing in your code.