Post JSON to web in excel vba

Question

I want to POST 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 to 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 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 ...

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 Acceptable reply from the website.

Answer 1

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 Body into a separate variable and debugging the value with http://jsonformatter.curiousconcept.com/ before sending.

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:

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