将JSON发布到Excel VBA中的Web

本文介绍了将JSON发布到Excel VBA中的Web的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想用一些VBA发布一些JSON:

I want to POST some JSON with some VBA:

Dim sURL As String, sHTML As String, sAllPosts As String Dim oHttp As Object Dim blWSExists As Boolean Set oHttp = CreateObject("MSXML2.XMLHTTP") sURL = "some webste" oHttp.Open "POST", sURL, False oHttp.setRequestHeader "Content-type", "application/json" oHttp.setRequestHeader "Accept", "application/json" oHttp.Send (mType = OPEN_SYSTEM_TRADE & systemOwnerId = 10) sHTML = oHttp.ResponseText Worksheets("Open1").Range("A1").Value = sHTML

要发送到网站的预定义格式是json中的描述，如下所示: {"mType":"OPEN_SYSTEM_TRADE","systemOwnerId":10,"systemId":16, etc}

The predefined format to be sent to the website is a description in json as follows : {"mType":"OPEN_SYSTEM_TRADE","systemOwnerId":10,"systemId":16, etc}

我的oHttp.Send行肯定是错误的，一旦我添加了更多参数，就会出现编译器错误 我发布了这个(无效的)代码，这是迄今为止我在网上可以找到的最好的代码(所有其他原因使我陷于其他我不理解的事情上……

My oHttp.Send line must be wrong, as soon as i add more arguments, i get a compiler error I publish this (not working) code cause its the best i could find on the web so far (all other get me stuck on other things that i don't understand ...

我还试图将json代码放在​​一个单元格中，将该单元格放入一个字符串中，然后发送这样的字符串:oHttp.Send(字符串)，这会导致网站发出Error 406 Not Acceptable答复.

I also tried to put the json code in a cell, put the cell in a string, and send the string like this : oHttp.Send (string), which results in a Error 406 Not Acceptable reply from the website.

推荐答案

JSON对其格式非常敏感，因此我将确保在发送所有内容之前都将其正确引用.我建议将Body拆分为一个单独的变量，并使用 jsonformatter.curiousconcept/调试值a>发送之前.

JSON can be very sensitive to how it's formatted, so I would make sure everything is quoted properly before it is sent. I would recommend splitting Body into a separate variable and debugging the value with jsonformatter.curiousconcept/ before sending.

Dim Body As String Body = "{""mType"":""OPEN_SYSTEM_TRADE"",""systemOwnerId"":10}" ' Set breakpoint here, get the Body value, and check with JSON validator oHttp.Send Body

在使用Salesforce的REST API时，我遇到了许多类似的问题，并将我的工作合并到一个可能对您有用的库中: github/VBA-tools/VBA-Web .使用该库，您的示例将如下所示:

I ran into many similar issues when working with Salesforce's REST API and combined my work into a library that may be useful to you: github/VBA-tools/VBA-Web. Using this library, your example would look like:

Dim Body As New Dictionary Body.Add "mType", "OPEN_SYSTEM_TRADE" Body.Add "systemOwnerId", 10 Dim Client As New WebClient Dim Response As WebResponse Set Response = Client.PostJson("somewebsite", Body) Worksheets("Open1").Range("A1").Value = Response.Content