XMLHttpRequest from VBA returns error 406 "Not Accepted"

Question

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".

How do you access the file using dev tools? I get the 406 error trying to bring up the page — NickSlash
– NickSlash, Commented May 31, 2020 at 21:46
@NickSlash You're right, when one tries to open this JSON directly by its url on the browser, one gets 406. But if you go to Firefox dev tools, Network tab, select the request, click on "Edit and Resend" and put the headers shown on the question (the same that are used when the JSON is retrieved by the webpage), it works. I edited the question with a link to the site that retrieves this JSON with the correct headers — Ravi
– Ravi, Commented May 31, 2020 at 23:21

SIM · Accepted Answer · 2020-06-01 06:10:27Z

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"}

Collectives™ on Stack Overflow

XMLHttpRequest from VBA returns error 406 "Not Accepted"

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related