Get multiple tables in one Excel web query

Question

I have am getting many tables from a web page to Excel using Web Queries. The tables in the web page are listed like this

A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
A13
A14
A15
A16
A17
B1
B2
B3
B4
B5
B6
B7
B8
B9
B10
B11
B12
B13
B14
B15
B16
B17
...

In Excel I need them to be like this:

A1 B1 C1 D1 E1 F1 G1 ...

A2 B2 C2 D2 E2 F2 G2 ...

I am getting all these tables by running several times this code, by just changing webtables from 1,18,35,52,69... to 2,19,36,53,70... 3,20,37,54,71... until 17,34,51,68,85... and range of putting data from A1 to AE1, BI1... :

With ActiveSheet.QueryTables.Add(Connection:="URL;http://domain.com", Destination:=Range("$A$1"))
    .Name = "table-01"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = _
    "1,18,35,52,69,86,103,120,137,154,171,188,205,222,239,256,273,290,307,324,341,358,375,392,409,426,443,460,477,494,511,528,545,562,579,596,613,630,647,664,681,698,715,732,749,766,783,800,817,834"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = True
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

The above code runs 17 times to get all the data and move it from all vertical to horizontal into Excel. But it takes a long time and I notice that for each webquery I am opening a new connection, get the data into Excel, close, move to next one, open connection, get the data into Excel, close ... and so on 17 times. But with a high load, the webserver responds sometimes with error and blank page, so I get in my Excel empty parts like:

A1 B1 C1 xx E1 F1 G1 ...

A2 B2 C2 xx E2 F1 G2 ...

I am wondering if is possible to do any of these options, whichever is easier and/or better:

From Excel, open one connection and get all the data in cycles then close connection, or
Excel detects that the webquery is not returning any data, so webquery is blank, and so it automatically retries that webquery until it gets data and then move to next query

EDIT : Graphical idea of how the tables should be arranged in one call

Option 1 is possible in a variety of methods. Using an HTTPXML object, it is typically called 'scraping'. — user4039065
Here's one. link. Click on the question's [xmlhttp-vba] or [html-parsing] tags for more examples. — user4039065
With HTPXML I will get all the data, but I must take table1, table17 ... and put them right below each other, and then table2, table18 put on the right and so on with all other tables, I do not know correctly how to move them because I do not have any reference how to know when finishes one table and begins next one... — g.e
To give an answer to your question, I would have to see the html code that makes up the web page holding the tables. There are simply too many methods available to put multiple tables on a web page to simply state 'do this' without seeing hte HTML layout first. — user4039065
@Jeeped please see sample code here link , from there all tables beginning with Belorussia Kup should be one next to each other horizontally, the same applies to Meksiko 2 and so on — g.e

genespos genespos · Accepted Answer · 2015-11-18T10:11:11

Have you tryed to add transpose?

With ActiveSheet.QueryTables.Add(Connection:="URL;http://domain.com", Destination:=Range("$A$1"))
    '...
    .Transpose = True
End With

Edit:

I was thinking about using transpose and change

Destination:=Range("$A$1")

into

Destination:=Range(StartingCell)'where StartigCell is valued to set the first empty row

so to have all data in columns instead of rows

Get multiple tables in one Excel web query

1 Answers