Publish JSON on the web in excel vba

Question

I want ALMOST 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.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 that will be sent to the website is a description in json as follows: {"mType":"OPEN_SYSTEM_TRADE","systemOwnerId":10,"systemId":16, etc}

My oHttp.Send line should be wrong, as soon as I add more arguments, I get a compiler error I publish this (not working) code, because its the best that I could find on the Internet so far (everyone else makes me fixate on other things that I do not understand ...

I also tried putting json code in a cell, putting the cell in a string and sending the string like this: oHttp.Send (string), which leads to an Error 406 Not Acceptable from the site.

Answer 1

JSON can be very sensitive to how it is formatted, so I would make sure everything is correctly specified before sending it. I would recommend splitting Body into a separate variable and debugging the value of http://jsonformatter.curiousconcept.com/ before submitting.

 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 

While working with the Salesforce REST API, I came across many similar problems and combined my work with a library that may be useful to you: https://github.com/VBA-tools/VBA-Web . Using this library, your example will look like this:

 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