Excel VBA Web Scraping

Question

I'm new to Excel VBA programming and need some help to debug my code for web scraping some data off of Amazon.

On my sheet1, I am listing the asins of products and trying to use this code to complete the URL to land onto the product page, then will the sales rank of the product via class ID or another html tag.

However, I keep getting the error:

User-Defined Type not Defined

I can't figure out where the bug is.

The code is listed below:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = Range("azAsin").Row And Target.Column = Range("azAsin").Column Then
     Dim IE As New InternetExplorer
     IE.Visible = True
     IE.Navigate "https://www.amazon.com/dp/" & Range("azAsin").Value
    Do
     DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE
    Dim pap As HTMLDocument
    Set pap = IE.document
    Dim sDoc As String
    sDoc = Doc.getElementsById("SalesRank")

    MsgBox sDoc
    End If
End Sub

Have you included a reference to the Microsoft Internet Controls library ? — Robin Mackenzie
– Robin Mackenzie, Commented Nov 16, 2017 at 5:39
The other option is to use late binding but I have vague recollection that it is not reliable when being used for classes used for web scraping. Statements such as Dim pap As HTMLDocument will become Dim pap As Object when you use late binding but then you don't have to set references. — shrivallabha.redij
– shrivallabha.redij, Commented Nov 16, 2017 at 6:29
Declare IE as object variable, CreateObject of ie application. — AntiDrondert
– AntiDrondert, Commented Nov 16, 2017 at 7:16

Kostas K. · Accepted Answer · 2017-11-16 07:34:57Z

2

Try this:

Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
'...

Dim objRank as Object
Set objRank = ie.Document.getElementById("SalesRank")

Dim rank As String
If Not objRank Is Nothing Then rank = objRank.innerText

answered Nov 16, 2017 at 7:34

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

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

Comments

QHarr · Accepted Answer · 2018-06-09 06:54:59Z

Here is a fully latebound version that also removes the need for a browser.

Observations on your code:

The other answer has corrected, but not mentioned, that in your existing script you attempt:

Dim sDoc As String
Doc = Doc.getElementsById("SalesRank")

getElementsById is invalid as Id should be singular and the syntax is getElementById. This will also return an object, not a string. so you would need ie.Document.getElementById("SalesRank").innerText to get a string; though it is better to set to an object first and test If Not obj Is Nothing Then, as indeed the former answerer has done.

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim html As Object, i As Long, URL As String
    URL = "https://www.amazon.com/dp/" & Range("azAsin").Value

    If Target.Row = Range("azAsin").Row And Target.Column = Range("azAsin").Column Then

        With CreateObject("MSXML2.serverXMLHTTP")
            .Open "GET", URL, False
            .send
            Set html = CreateObject("HTMLFile")
            html.body.innerHTML = .responseText
        End With

        For i = 0 To html.all.Length - 1
            If html.all(i).ID = "SalesRank" Then MsgBox html.all(i).innerText
        Next i
    End If
End Sub

Collectives™ on Stack Overflow

Excel VBA Web Scraping

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related