VBA to loop through multiple HTML tables

Question

Here is the link from where I'm trying to return price values only: https://www.express-supp...

I have got a VBA script which returns all product-grid-details table values into the workbook but some of the values goes into the wrong columns which does not allow pivot pivot table to be generated. But if I change this code to generate table called price-box it doesn't return any values at all.

I think HTML tables on the page is out of order and not sequenced with each other and that is what it makes the data to be out of columns. As a solution I would like the VBA to return ONLY item names and prices of the page but not the whole lot. How do I do it?

Example of how table is returned into the workbook if choosing to return product-grid-details:

Here is the code:

     With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", "https://www.express-supplements.co.uk/catalogsearch/result?q=Optimum+Nutrition", False
    .send
    oHtml.body.innerHTML = .responseText
    Debug.Print
 End With

 ReDim a(1 To 100000, 1 To 60)
 For Each oElement In oHtml.getElementsByClassName("product-grid-details")
    i = i + 1
    x = Split(oElement.innerText, vbCr)

    For ii = 1 To UBound(x)
        a(i, 1) = nowDate
        a(i, 2) = nowTime
        a(i, 3) = weblinks(webX, 1)
        a(i, 4) = weblinks(webX, 2)
        a(i, ii + 4) = Trim$(x(ii))
    Next

 Next oElement

    With SHwebdata
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Cells(LastRow + 1, 1).Resize(i, UBound(a, 2)) = a
        i = 0
    End With

SIM · Accepted Answer · 2017-07-28 20:47:06Z

2

Here you go. Just run it and get the results as you asked for:

Sub Web_Data()
    Dim http As New XMLHTTP60, html As New HTMLDocument
    Dim topic As HTMLHtmlElement

    With http
        .Open "GET", "https://www.express-supplements.co.uk/catalogsearch/result?q=Optimum%20Nutrition", False
        .send
        html.body.innerHTML = .responseText
    End With

    For Each topic In html.getElementsByClassName("product-grid-details")
        With topic.getElementsByClassName("product-name")
            If .Length Then x = x + 1: Cells(x, 1) = .item(0).innerText
        End With
        With topic.getElementsByClassName("price")
            If .Length Then Cells(x, 2) = .item(0).innerText
        End With
    Next topic
End Sub

answered Jul 28, 2017 at 20:47

SIM

22.5k6 gold badges45 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Martin Over a year ago

You are a genius! Working perfectly!

Martin Over a year ago

Could you please have a look at this link to see whether it is possible to return item names and prices from them multiple table using same approach as you have just posted? I think it should follow by getElementsByClassName("category-products") and then ("product-name") and ("price") but for some reason it doesn't work. Could it be that .Item(0) number has to be changed to return values?

SIM Over a year ago

No problem. Just open a new thread and give here a link. I'm gonna take a look into that. Thanks.

Martin Over a year ago

Here it is.

Martin Over a year ago

could you please have a look at this

Collectives™ on Stack Overflow

VBA to loop through multiple HTML tables

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related