Post JSON to web in excel vba

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 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: 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("", Body)

Worksheets("Open1").Range("A1").Value = Response.Content

Leave a Comment