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

I did that and now i am ending up with Error 438 Object Does'nt Support this property at pricedata. what should be object data here — Sumeet Pujari
– Sumeet Pujari, Commented Dec 25, 2015 at 14:42

Reza Aghaei · Accepted Answer · 2015-12-25 17:06:51Z

3

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

edited Dec 25, 2015 at 17:06

answered Dec 25, 2015 at 14:41

Reza Aghaei

126k18 gold badges239 silver badges447 bronze badges

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

5 Comments

Sumeet Pujari Over a year ago

It works for stack over flow but when i try for amazon to get the seller names and pricing through this link it shows nothing with the class name amazon.in/gp/offer-listing/B00EYCBGNA/…

Reza Aghaei Over a year ago

The post answers your main question but you should use suitable values and logic to be able to extract prices. The answer is really better this way. For example you can extract prices this way: Set elements = html.getElementsByClassName("olpOfferPrice") and it returns an array with length of 6.

Reza Aghaei Over a year ago

I also added your specific case for that amazon link. See edited answer and also pay attention to those 2 While commands.

Reza Aghaei Over a year ago

Also probably you will see price using your currency unit. Here is shows Rs.

Sumeet Pujari Over a year ago

Yup got it, Just want to mention that we did not did ie.Quit. The code might not start again once completed

Collectives™ on Stack Overflow

Excel VBA - Extracting data from web page

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related