Get Data using MSXML2.XMLHTTP

Question

i am trying to get data using MSXML2.XMLHTTP but it didn't work

any ideas?

Sub getdata

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As String
Dim sht As Worksheet


Application.DisplayAlerts = False
Set sht = ActiveSheet


    On Error Resume Next
    website = "https://shopee.co.id/AFI-EC-Tshirt-Yumia-(LD-90-P-57)-i.10221730.5568491283"
    Set request = CreateObject("MSXML2.XMLHTTP")

   request.Open "GET", website, False
   request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
   request.send
   response = StrConv(request.responseBody, vbUnicode)
   html.DocumentElement.innerHTML = response

    
    price = html.querySelector("div.AJyN7v")(0).innerText
    
    Debug.Print price
    
    Application.StatusBar = ""
    On Error GoTo 0
Application.DisplayAlerts = True``
End Sub

I have done many ways but still not working , hope someone can help me

"didn't work" is not a useful description of what happens when you run your code. Do you get an error? If Yes what error and which line. Please review How to Ask and consider improving your post with some additional details. — Tim Williams
– Tim Williams, Commented Feb 25, 2021 at 22:56
there is an error message Object variable or With block variable not set — anggun
– anggun, Commented Feb 26, 2021 at 0:03

QHarr · Accepted Answer · 2021-02-26 01:02:07Z

1

Pretty much everything on that page requires javascript to load. Javascript doesn't run with xmlhttp request to landing page so price never gets retrieved..

The price is being retrieved dynamically from an additional API call returning json.

If you examine the url you will have the following:

https://shopee.co.id/AFI-EC-Tshirt-Yumia-(LD-90-P-57)-i.10221730.5568491283

The last set of consecutive numbers is the product id i.e. 5568491283.

If you open the network tab of dev tools F12, and press F5 to refresh the web traffic that updates the page, then check on the xhr only traffic, then input your product id into the search box, the first result retrieved is the xhr which is returning the price:

https://shopee.co.id/api/v2/item/get?itemid=5568491283&shopid=10221730

The response is json so you will need a json parser to extract the result (or use regex on string - less preferable)

In the headers sub-tab you can view info about the xhr request made.

Check the terms and conditions to see if scraping allowed and also whether there is an public API for retrieving this data.

answered Feb 26, 2021 at 1:02

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

3 Comments

SIM Over a year ago

Thanks for this elaborative visual. It will immensely help future readers to understand what really goes on in the background.

anggun Over a year ago

how to parser json ?

QHarr Over a year ago

stackoverflow.com/search?q=json+user%3A9912714+is%3Aanswer , stackoverflow.com/a/66146810

Collectives™ on Stack Overflow

Get Data using MSXML2.XMLHTTP

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related