CODEWITHSUNDEEP
excelvbaweb-scraping
Anish Ardjoon
Anish Ardjoon

Reputation: 91

Scraping website using Excel VBA and XML

I am trying to scrape Newegg website to get price of products. When I run this piece of code it does the job.

Sub test()

Dim objIE As InternetExplorer
Set objIE = New InternetExplorer

objIE.Visible = True
objIE.Navigate "https://www.newegg.com/Product/Product.aspx?item=1TS-000E-083F2"

Do While objIE.ReadyState <> READYSTATE_COMPLETE
Loop

    Debug.Print objIE.Document.getElementsByClassName("price-current")(0).Children(1).innerText

End Sub

However, when I try to use XML for faster executing I am having a run time error 91: Object variable or With block variable not set

Below is the piece of code I am trying to implement.

Sub testxml()

Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument



XMLPage.Open "GET", "https://www.newegg.com/Product/Product.aspx?item=1TS-000E-083F2", False
XMLPage.send

HTMLDoc.body.innerHTML = XMLPage.responseText


Debug.Print HTMLDoc.getElementsByClassName("price-current")(0).Children(1).innerText
End Sub

Upvotes: 1

Views: 1923

Answers (2)

Ryszard Jędraszyk
Ryszard Jędraszyk

Reputation: 2412

You are trying to get innerText property for 2nd Child of collection element obtained through this code:

HTMLDoc.getElementsByClassName("price-current")(0)

This error happens, because "Object" from error prompt which is HTMLDoc.getElementsByClassName("price-current")(0) does not exist on this page.

You can check it with:

Debug.Print HTMLDoc.getElementsByClassName("price-current").Length

It will be 0, meaning no element with this class exists. If it exists for IE automation, it means that this element is part of HTML generated through JavaScript and XMLHTTP request will not have it in its response.

Upvotes: 4

Radek Piekn&#253;
Radek Piekn&#253;

Reputation: 145

does this help?

Dim XMLPage As Object
Dim HTMLDoc As Object


Set XMLPage = New MSXML2.XMLHTTP60
Set HTMLDoc = New MSHTML.HTMLDocument

There are some articles on stack about when to use new etc. so I am not gonna cover it.

Upvotes: 0

Related Questions