在 excel vba 中将 JSON 发布到网络
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21021540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Post JSON to web in excel vba
提问by Veronique
I want to POST some JSON with some VBA:
我想用一些 VBA 发布一些 JSON：
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.com"
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
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}
要发送到网站的预定义格式是 json 中的描述，如下所示：
{"mType":"OPEN_SYSTEM_TRADE","systemOwnerId":10,"systemId":16, etc}
My oHttp.Sendline 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 ...
我的oHttp.Send线路一定是错误的，一旦我添加更多参数，我就会收到编译器错误我发布了这个（不工作）代码，因为它是迄今为止我在网上能找到的最好的代码（所有其他让我卡在其他事情上不明白...
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 Acceptablereply from the website.
我还尝试将json代码放在一个单元格中，将单元格放在一个字符串中，然后像这样发送字符串：（oHttp.Send字符串），这会导致Error 406 Not Acceptable网站的回复。
回答by Tim Hall
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 Bodyinto a separate variable and debugging the value with http://jsonformatter.curiousconcept.com/before sending.
JSON 可能对其格式非常敏感，因此我会确保在发送之前正确引用所有内容。我建议在发送之前拆分Body为一个单独的变量并使用http://jsonformatter.curiousconcept.com/调试该值。
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
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: https://github.com/VBA-tools/VBA-Web. Using this library, your example would look like:
我在使用 Salesforce 的 REST API 时遇到了许多类似的问题，并将我的工作合并到一个可能对您有用的库中：https: //github.com/VBA-tools/VBA-Web。使用此库，您的示例将如下所示：
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.com", Body)
Worksheets("Open1").Range("A1").Value = Response.Content
