Webqueries multiple tables into one column

Question

I've made a code that downloads data using webquery from website (several pages) in a loop. Everything is great but instead of downloading data in the same column ("B2") if to set ActiveSheet.QueryTables(1) it creates one table starting in B2 but on the next loop (next webpage) it will overwrite the previously downloaded data again starting in B2.

It have to add next webpage data into worksheet in another range of cells (100 cells down from B2 in the same column) but even though I've changed the range the data is downloaded in the same position.

If to change i in ActiveSheet.QueryTables(i) it creates multiple tables in different columns (not in the same one).

With ActiveSheet.QueryTables(i)
    .Connection = "URL;" & page
    .Destination = Range(Cells(n + 1, 2), Cells(n + 100, 2))
    .Refresh
End With
i = i + 1

Kazimierz Jawor · Accepted Answer · 2015-02-02T14:47:11.593

0

Possibly this solution will solve your problem (assuming that n starts with 1):

.Destination = Range(Cells((n-1)*100 + 2, 2), Cells((n * 100) + 1, 2))

BTW, what you rather need as a .Destination value is this line:

.Destination = Cells((n-1)*100 + 2, 2)

See this MSDN Reference for .Destination property.

edited Feb 02 '15 at 14:47

answered Feb 02 '15 at 13:54

Kazimierz Jawor

18,861
7
35
55

Thank you @KazJaw , but when I clear previous data an error occured "Subscript out of range" – Samuel Feb 02 '15 at 14:24
I think your loop starts with n=0, as I said, I assumed n starts with 1. Or in which line you have an error? – Kazimierz Jawor Feb 02 '15 at 14:41

Webqueries multiple tables into one column

1 Answers1