CODEWITHSUNDEEP
vbaexcelinternet-explorerautomation
thoele
thoele

Reputation: 23

Extracting a value in a class from Internet Explorer using Visual Basic

With this code, I want to retrieve the value a specific tag from a website and put it on my spreadsheet:

Sub get_tit()
    Dim wb As Object
    Dim doc As Object
    Dim sURL As String
    Dim lastrow As Long

    lastrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

    Set wb = CreateObject("internetExplorer.Application")
    wb.navigate "https://www.tyre-shopper.co.uk/search/205-55-16/V/91"
    Do While wb.Busy
    Loop

    Set doc = wb.document

    Price = SelectNodes("#more-tyres > li:nth-child(4) > div > div.result-buy > form > span.tyre-price > div.tyre-price-cost.tyres-1 > strong")
    Range("A5").Value = Price
End Sub

I tried to use the CSS path to select it as a node but couldn't. I have also tried to select it from the class but again it did not work

This is the code from the website, from which I want to retrieve the value 57.50

<span class="tyre-price">
    Fully Fitted Price
        <div class="tyre-price-cost tyres-1">

            <strong>&pound;57.50</strong>
            </div><div class="tyre-price-cost tyres-2" style="display:none">

            <strong>&pound;115.00</strong>
            </div><div class="tyre-price-cost tyres-3" style="display:none">

            <strong>&pound;172.50</strong>
            </div><div class="tyre-price-cost tyres-4" style="display:none">

            <strong>&pound;230.00</strong>
            </div><div class="tyre-price-cost tyres-5" style="display:none">

            <strong>&pound;287.50</strong>
            </div>
        </span>

Upvotes: 2

Views: 166

Answers (1)

user4039065
user4039065

Reputation:

I've never had much success with directly extracting elements according to the getElementsByClassName method but cycling through the collection returned by the getElementsByTagName method and comparing the class property seems to work as least as well.

Sub get_tit()
    Dim wb As Object
    Dim doc As Object
    Dim sURL As String
    Dim lastrow As Long
    Dim iDIV As Long, sPrice As Variant, sib As Long
    Dim eSIB As IHTMLElement

    lastrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

    Set wb = CreateObject("internetExplorer.Application")
    wb.Visible = True
    wb.navigate "https://www.tyre-shopper.co.uk/search/205-55-16/V/91"
    Do While wb.Busy And wb.readyState <> 4
        DoEvents
    Loop

    Set doc = wb.document

    With doc.body
        sPrice = Array(0, 0, 0, 0)
        For iDIV = 0 To .getElementsByTagName("div").Length - 1
            With .getElementsByTagName("div")(iDIV)
                Select Case .className
                    Case "tyre-price-cost tyres-1"
                        sPrice(0) = .innerText
                    Case "tyre-price-cost tyres-2"
                        sPrice(1) = .innerText
                    Case "tyre-price-cost tyres-3"
                        sPrice(2) = .innerText
                    Case "tyre-price-cost tyres-4"
                        sPrice(3) = .innerText
                        With Sheet1
                            .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = sPrice(0)
                            .Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = sPrice(1)
                            .Cells(Rows.Count, "A").End(xlUp).Offset(0, 2) = sPrice(2)
                            .Cells(Rows.Count, "A").End(xlUp).Offset(0, 3) = sPrice(3)
                        End With
                        sPrice = Array(0, 0, 0, 0)
                    Case Else
                End Select
            End With
        Next iDIV
    End With
End Sub

        tyre scrape

fwiw, I believe you would be better served with an IXMLHTTPRequest based scrape than one using the InternetExplorer object.

Upvotes: 1

Related Questions