VBA POST json 到 API
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29015207/
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
VBA POST json to API
提问by deving
I am trying to write VBA to post json to an api and parse the results into a worksheet. I can generate the JSON and am confident I can parse the result into what I need.
我正在尝试编写 VBA 将 json 发布到 api 并将结果解析到工作表中。我可以生成 JSON，并且有信心可以将结果解析为我需要的内容。
I know there are online tools to convert json to vba and back and browser add ins to post requests but I am the only one in the office that can do this so if I'm sick or on leave I would like to automate it. To do that I need to send the json and maybe store the response so I can parse it.
我知道有一些在线工具可以将 json 转换为 vba 并返回，还有将浏览器插件转换为发布请求的工具，但我是办公室中唯一可以执行此操作的工具，因此如果我生病或休假，我想将其自动化。为此，我需要发送 json 并可能存储响应，以便我可以解析它。
I'm new to coding so posting a request like this is over my head. So far I have the following code to write the json. I would appreciate any help in getting me started. If needed I can post a sample of the json or the api I would like to post it to.
我是编码新手，所以发布这样的请求超出了我的头脑。到目前为止，我有以下代码来编写json。我将不胜感激任何帮助我开始。如果需要，我可以发布我想发布的 json 或 api 示例。
Apologies for the poor code I know I can improve it but want to get the json response as I think it will be the most challenging part.
为糟糕的代码道歉我知道我可以改进它但想要得到 json 响应，因为我认为这将是最具挑战性的部分。
EDIT Have made some progress. Can now send a JSON string to the URL and get the response. However it is always returning a failure:
编辑已经取得了一些进展。现在可以向 URL 发送 JSON 字符串并获得响应。但是它总是返回失败：
"{ ""message"": ""An error has occurred."" }"
"{ ""message"": ""发生错误。"" }"
If I manually send the json with httpRequestor the result is returned correctly. This seems to suggest that somewhere in the code the JSON is getting mixed up or modified somehow when it is being posted.
如果我使用 httpRequestor 手动发送 json，结果将正确返回。这似乎表明代码中的某处 JSON 在发布时被混淆或以某种方式修改。
Updated code below. (Have removed any reference to actual data)
更新了下面的代码。（已删除对实际数据的任何引用）
EDIT 2 fixed and working. Removed quotes from
编辑 2 已修复并正常工作。删除了引号
objHTTP.send ("Json")
objHTTP.send ("Json")
Private Sub CommandButton21_Click()
Dim h_1 As String
Dim h_2 As String
h_1 = Range("A1")
h_2 = Range("B1")
h_3 = Range("C1")
h_4 = Range("D1")
h_5 = Range("E1")
h_6 = Range("F1")
sv_1 = 2
sv_2 = 2
sv_3 = 2
sv_4 = 2
sv_5 = 2
sv_6 = 2
For f = 15 To 21
v_1 = Range("A" & sv_1)
v_2 = Range("B" & sv_2)
v_3 = Range("C" & sv_3)
v_4 = Range("D" & sv_4)
v_5 = Range("E" & sv_5)
v_6 = Range("F" & sv_6)
y = "[{""" & h_1 & """:""" & v_1 & """,""" & h_2 & """:""" & v_2 & """,""" & h_3 & """:""" & v_3 & """,""" & h_4 & """:""" & v_4 & """,""" & h_5 & """:""" & v_5 & """,""" & h_6 & """:""" & v_6 & """ }]"
Range("A" & f).Value = y
sv_1 = sv_1 + 1
sv_2 = sv_2 + 1
sv_3 = sv_3 + 1
sv_4 = sv_4 + 1
sv_5 = sv_5 + 1
sv_6 = sv_6 + 1
Next f
Dim objHTTP As Object
Dim Json As String
Json = Range("A15")
Dim result As String
'Set objIE = CreateObject("InternetExplorer.Application") ' Don't think this is needed
'objIE.navigate "about:blank" ' Don't think this is needed
'objIE.Visible = False ' Don't think this is needed
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URl = "http://myApi/iSendJsonTo"
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/json"
objHTTP.send ("Json")
result = objHTTP.responseText
'objIE.document.Write result ' Don't think this is needed
'Some simple debugging
Range("A25").Value = result
Range("A26").Value = Json
Set objHTTP = Nothing
回答by deving
Here is the code that is sending the JSON, cleaned up a little.
这是发送 JSON 的代码，稍微清理了一下。
Dim objHTTP As Object
Dim Json As String
Json = Range("A15") 'here I am pulling in an existing json string to test it. String is created in other VBA code
Dim result As String
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URl = "http://myApi/iSendJsonto/"
objHTTP.Open "POST", URl, False
objHTTP.setRequestHeader "Content-type", "application/json"
objHTTP.send (Json)
result = objHTTP.responseText
'Some simple debugging
Range("A25").Value = result
Range("A26").Value = Json
Set objHTTP = Nothing
