CODEWITHSUNDEEP
excelvbaweb-scrapinghttpsxmlhttprequest
Ravi
Ravi

Reputation: 3

XMLHttpRequest from VBA returns error 406 "Not Accepted"

I'm trying to do some scraping using Excel VBA, specifically, to get a JSON from a server. I'm using exactly the same request headers that show on the browsers dev tools, but when I do this request from Excel I always get a 406 ("Not accepted") answer from the server. What am I missing?

Sub getJSON()

   Dim XHR As New MSXML2.ServerXMLHTTP60

   XHR.Open "GET", "https://www.magazineluiza.com.br/produto/calculo-frete/08226021/615254900/frigelar.json", False

   XHR.setRequestHeader "Host", "www.magazineluiza.com.br"
   XHR.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:76.0) Gecko/20100101 Firefox/76.0"
   XHR.setRequestHeader "Accept", "application/json, text/javascript, */*; q=0.01"
   XHR.setRequestHeader "Accept-Language", "pt-BR,pt;q=0.8,en-US;q=0.5,en;q=0.3"
   XHR.setRequestHeader "Accept-Encoding", "gzip, deflate, br"
   XHR.setRequestHeader "X-Requested-With", "XMLHttpRequest"
   XHR.setRequestHeader "Connection", "keep-alive"
   XHR.setRequestHeader "Referer", "https://www.magazineluiza.com.br/refrigerador-expositor-para-bebidas-metalfrio-com-controlador-eletronico-497-litros-vb52r-220v/p/6152549/pi/expb/"
   XHR.setRequestHeader "TE", "Trailers"

   XHR.send

   Debug.Print XHR.responseText

End Sub

This is the answer I get on the browser:

{"address":{"address":"18 DE ABRIL","area":"CIDADE ANTONIO ESTEVAO DE CARVALHO","city":"SAO PAULO","state":"SP","zip_code":"08226021"},"delivery":[{"description":"Entrega padrão","distribution_center":0,"is_deadline":true,"price":"263.91","time":13,"zip_code_restriction":false}],"is_delivery_unavailable":false,"zip_code":"08226021"}

Edit: This JSON is retrieved by the browser when I add the zip code "08226021" on the right side of this page and click "Ok".

Upvotes: 0

Views: 424

Answers (1)

SIM
SIM

Reputation: 22440

Try this to get the required response:

Sub FetchJsonResponse()
    Const Url$ = "https://www.magazineluiza.com.br/produto/calculo-frete/08226021/615254900/frigelar.json"

    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", Url, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; ) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.61 Safari/537.36"
        .setRequestHeader "Referer", "https://www.magazineluiza.com.br/refrigerador-expositor-para-bebidas-metalfrio-com-controlador-eletronico-497-litros-vb52r-220v/p/6152549/pi/expb/"
        .setRequestHeader "x-requested-with", "XMLHttpRequest"
        .send
        Debug.Print .responseText
    End With
End Sub

This is the output it produces:

{"address": {"address": "18 DE ABRIL", "area": "CIDADE ANTONIO ESTEVAO DE CARVALHO", "city": "SAO PAULO", "state": "SP", "zip_code": "08226021"}, "delivery": [{"description": "Entrega padr\u00e3o", "distribution_center": 0, "is_deadline": true, "price": "263.91", "time": 13, "zip_code_restriction": false}], "is_delivery_unavailable": false, "zip_code": "08226021"}

Upvotes: 1

Related Questions