VBA POST JSON到API [英] VBA POST json to API
问题描述
我正在尝试编写VBA以将json发布到api并将结果解析到工作表中.我可以生成JSON，并且有信心可以将结果解析为所需的内容.
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.
我知道有在线工具可以将json转换为vba并返回，浏览器添加插件可以发布请求，但是我是办公室中唯一可以做到这一点的工具，因此，如果我生病或休假，我想自动化它.为此，我需要发送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.我会很高兴能帮助我入门.如果需要，我可以发布我想发布到的json或api示例.
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响应，因为我认为这将是最具挑战性的部分.
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.
EDIT取得了一些进展.现在可以将JSON字符串发送到URL并获取响应.但是，它总是返回失败:
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:
" { "消息":"发生错误." }"
"{ ""message"": ""An error has occurred."" }"
如果我使用httpRequest手动发送json，则结果将正确返回. 这似乎表明JSON在发布时在代码中的某处变得混乱或被修改.
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.
下面的更新代码. (已删除对实际数据的任何引用)
Updated code below. (Have removed any reference to actual data)
编辑2已修复，可以正常工作. 从
EDIT 2 fixed and working. Removed quotes from
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
推荐答案
这是发送JSON的代码，需要进行一些清理.
Here is the code that is sending the JSON, cleaned up a little.
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
这篇关于VBA POST JSON到API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
