XHR请求使用带有JSON响应的VBA [英] XHR request using VBA with JSON response

问题描述

我正在尝试从电子商务时尚网站获取一些数据。当我在网站上打开一个类别时，我可以看到48个项目，最后加载 按钮。当我点击那个按钮，我看到接下来的48个项目。反手发生的是，当我点击加载更多按钮时，将发送一个XHR发布请求，并以JSON格式返回响应。我想自动执行此搜索并捕获Excel表中的响应数据。我是编程新手，不熟悉先进的脚本语言，所以我正在VBA上工作。我的请求正在提交，但没有得到回复。我的类别页面是 http://www.myntra.com/_ ，请求发送的链接是 http://www.myntra.com/searchws/search/styleids2 。
这是我的代码：

  Sub post（）
 Dim objHTTP As Object 
 Dim结果As String 
设置objIE = CreateObject（InternetExplorer.Application）
 objIE.navigateabout：blank
 objIE.Visible = True 
设置objHTTP = CreateObject（MSXML2 .ServerXMLHTTP）
 URL =http://www.myntra.com/searchws/search/styleids2
 objHTTP.OpenPOST，URL，False 
 objHTTP.setRequestHeader用户-Agent，Mozilla / 4.0（兼容; MSIE 6.0; Windows NT 5.0）
 objHTTP.setRequestHeaderContent-type，application / x-www-form-urlencoded
 objHTTP.send （query =（full_text_myntra：（_）AND（statusid：1008））& start = 0& rows = 500& facet = true& facetField = []）
 result = objHTTP.responsetext 
 objIE.document.Write result 
 
 Set objHTTP = Nothing 
 End Sub 
  

解决方案

当我尝试运行查询wi时，我有不少415个错误邮递员，它看起来像API期待JSON而不是形式urlencoded和参数需要包装在一个数组中，所以我会检查你的代码：

 [{query： （full_text_myntra：（_）AND（statusid：1008）），  启动 ：0  行 ：500  面 ：真  facetField ：[]} ]
  

此外，我建议使用类似 Excel-REST （我为我正在运行的这种情况而制作）帮助创建请求并处理JSON：

  Dim MyntraClient作为新的RestClient 
 MyntraClient.BaseUrl =http://www.myntra.com/
 
'with inline JSON 
 Dim json As String 
 json =[{query：（full_text_myntra：（_）AND（statusid：1008）），开始：0，rows：500facet：truefacetField：[]}]
 
 Dim Response As RestResponse 
设置响应= MyntraClient.PostJSON（searchws / search / styleids2，json）
 
'手工创建json字符串并不好用，而是通过Dictionary / Collection / Array创建它b $ b Dim SearchParameters As New Dictionary 
 SearchParameters.Addquery，（full_text_myntra：（_）AND（statusid：1008））
 SearchParameters.Addstart，0 
 SearchParameters.Addrows，500 
 SearchParameters.Addfacet，True 
 SearchParameters.AddfacetField，Array（）
 
设置Response = MyntraClient.PostJSON（ searchws / search / styleids2，Array（SearchParameters））
 
'检查状态，收到的内容，或直接使用数据执行某些操作
 Debug.Print Response.StatusCode 
调试。打印Response.Content 
 Debug.Print Response.Data（response1）（totalProductsCount）
  

I am trying to fetch some data from an e-commerce fashion website. When i open a category on website I can see 48 items and a load more button in the end. When i click on that button, i see the next 48 items. What happens in backhand is, when i click that load more button an XHR post request is sent and response is returned in JSON format. I want to automate this search and capture the response data in an excel sheet. I am new to programming and not familiar with advance scripting languages so i am working on VBA. My request is getting submitted but not get the response. My category page is http://www.myntra.com/_ and the link where request is sent is http://www.myntra.com/searchws/search/styleids2. Here is my code :

    Sub post()
    Dim objHTTP As Object
    Dim result As String
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.navigate "about:blank"
    objIE.Visible = True
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = "http://www.myntra.com/searchws/search/styleids2"
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
   objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
   objHTTP.send ("query=(full_text_myntra:(_)AND(statusid:1008))&start=0&rows=500&facet=true&facetField=[]")
   result = objHTTP.responsetext
   objIE.document.Write result

   Set objHTTP = Nothing
  End Sub     

解决方案

I got quite a few 415 errors when trying to run the query with Postman and it looks like the API was expecting JSON rather than form-urlencoded and the parameters needed to be wrapped in an array so I would check that in your code:

"[{""query"":""(full_text_myntra:(_)AND(statusid:1008))"",""start"":0,""rows"":500,""facet"":true,""facetField"":[]}]"

Additionally, I would recommend using something like Excel-REST (which I made for cases just like this that I was running into) to help with creating the request and handling JSON:

Dim MyntraClient As New RestClient
MyntraClient.BaseUrl = "http://www.myntra.com/"

' With inline JSON
Dim json As String
json = "[{""query"":""(full_text_myntra:(_)AND(statusid:1008))"",""start"":0,""rows"":500,""facet"":true,""facetField"":[]}]"

Dim Response As RestResponse
Set Response = MyntraClient.PostJSON("searchws/search/styleids2", json)

' It's no fun creating json string by hand, instead create it via Dictionary/Collection/Array
Dim SearchParameters As New Dictionary
SearchParameters.Add "query", "(full_text_myntra:(_)AND(statusid:1008))"
SearchParameters.Add "start", 0
SearchParameters.Add "rows", 500
SearchParameters.Add "facet", True
SearchParameters.Add "facetField", Array()

Set Response = MyntraClient.PostJSON("searchws/search/styleids2", Array(SearchParameters))

' Check status, received content, or do something with the data directly
Debug.Print Response.StatusCode
Debug.Print Response.Content
Debug.Print Response.Data("response1")("totalProductsCount")

这篇关于XHR请求使用带有JSON响应的VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！