Excel VBA - Extracting data from web page

Question

I am trying to pull seller information from Amazon page with price by automating web browser. I am trying to run the below code, but the error I am getting is:

Object Variable or With Block variable not set.

Can someone guide me where i am going wrong.

Option Explicit
Sub RunNewModule()  
    Dim ie As InternetExplorer
    Dim html As HTMLDocument
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False
    ie.Navigate "http://www.amazon.com/gp/offer-listing/B00SVA81Z2/ref=dp_olp_new_mbc?ie=UTF8&condition=new"
    Dim priceData As Variant
    Dim sellerdata As Variant
    Dim item As Variant
    Dim cntr As Integer
    priceData = html.getElementsByClassName("olpOfferPrice").getElementsByTagName("span")(0).innerText
    cntr = 1
    For Each item In priceData
        Range("B" & cntr) = item.innerText
        cntr = cntr + 1
    Next item
    sellerdata = html.getElementsByClassName("olpSellerName").getElementsByTagName("span")(0).innerText    
    cntr = 1
    For Each item In sellerdata
        Range("A" & cntr) = item.innerText
        cntr = cntr + 1
    Next item  
End Sub

Solution

You didn't assign html and it's null now.

You should assign it this way:

Set html= ie.Document

To get an element by it's class name:

Dim ie As InternetExplorer
Dim html As IHTMLDocument
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = False
ie.Navigate "http://stackoverflow.com/questions/34463544/vba-fetching-data-from-class-name"
While ie.Busy
    DoEvents
Wend
While ie.ReadyState < 4
    DoEvents
Wend
Set html = ie.Document
Dim elements As IHTMLElementCollection
Set elements = html.getElementsByClassName("question-hyperlink")
If elements.Length > 0 Then
    MsgBox elements(0).innerText
End If
ie.Quit
Set ie = Nothing

Don't forget to add reference to:

• Microsoft Internet Controls
• Microsoft Html Object library

---

For that amazon link:

Dim ie As InternetExplorer
Dim html As HTMLDocument
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = False
ie.Navigate "http://www.amazon.in/gp/offer-listing/B00EYCBGNA/ref=dp_olp_new_mbc?ie=UTF8&condition=new"
While ie.Busy
    DoEvents
Wend
While ie.ReadyState < 4
    DoEvents
Wend

Set html = ie.Document
Dim elements As IHTMLElementCollection
Set elements = html.getElementsByClassName("olpOfferPrice")
For i = 0 To elements.Length - 1
     Sheet1.Range("A" & (i + 1)) = elements(i).innerText
Next i

Set elements = html.getElementsByClassName("olpSellerName")
For i = 0 To elements.Length - 1
    Sheet1.Range("B" & (i + 1)) = elements(i).innerText
Next i
ie.Quit
Set ie = Nothing