CODEWITHSUNDEEP
vbaexcelweb-scrapinghtml-table
Martin
Martin

Reputation: 459

Scraping HTML tables using VBA

Using the below script I'm successfully returning values from the HTML tables into the workbook from the following links: link1 and link2. But when I'm trying to use the same script for the following link3, it does not return anything back. I think it is due to complex HTML table structure existing on the website. I believe code requires .Item(0) number to be adjusted due to table complexity, please advice.

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

    With http
        .Open "GET", "http://www.dolphinfitness.co.uk/en/optimum-nutrition/", False
        .send
        html.body.innerHTML = .responseText
    End With

    For Each topic In html.getElementsByClassName("category-products")
        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

Upvotes: 0

Views: 2318

Answers (1)

SIM
SIM

Reputation: 22440

The very site you mentioned in your post is a bit tricky when it comes to parse the price of different products. Few products have got original price with it and the rest have got special price with them. You can't parse both of them all at once until you apply a technique with your expression. I've written an xpath which is able to deal with them and you will be able to get them all. Here is the script:

Sub Body_Building()
    Dim driver As New WebDriver, post As Object

    With driver
        .Start "chrome", "http://www.bodybuildingwarehouse.co.uk"
        .Get "/optimum-nutrition?limit=all"
    End With

    On Error Resume Next
    For Each post In driver.FindElementsByClass("grid-info")
        i = i + 1: Cells(i, 1) = post.FindElementByClass("product-name").Text
        Cells(i, 2) = post.FindElementByXPath(".//span[@class='regular-price']//span[@class='price']|.//p[@class='special-price']//span[@class='price']").Text
    Next post
End Sub

Let me know if you have any problem executing the script. Btw, selenium binding with vba doesn't have any property to shun "On error resume next" so i put it before the loop. Thanks.

Upvotes: 1

Related Questions