问

在excel vba中将JSON发布到Web

手机用户2502922793 发布于 2023-02-04 19:48

post

我想用一些VBA POST一些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

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

我的oHttp.Send行一定是错的,只要我添加更多的参数,我得到编译错误我发布这个(不工作)代码导致它到目前为止我能在网上找到的最好(所有其他让我卡在其他东西,我不明白......

我还尝试将json代码放在一个单元格中,将单元格放在一个字符串中,并发送如下字符串:( oHttp.Send字符串),这会导致Error 406 Not Acceptable网站的回复.

1 个回答

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
```
在使用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
```
2023-02-04 19:50 回答

永恆極光_822

撰写答案

今天，你开发时遇到什么问题呢？

立即提问

热门标签