Reputation: 3
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
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