3

I'm trying to send a JSON object from a VBA application in Excel. The code below sends the request correctly, however I can't figure out how to make the request with the JSON object in the body.

Sub Post()

    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = "http://localhost:3000/test"
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send "test=6"

End Sub

If I try for instance to send "{test:6, test2: 7}" and log the body of the request on the server I get { '{parts:6, test: 7}': '' }

zforgo
  • 2,508
  • 2
  • 14
  • 22
mattc19
  • 678
  • 1
  • 11
  • 23

2 Answers2

6

As you probably already know, JSON structure can really produce serious headaches. In your case, I would say its all about the quotes, as you can see in here if you try to validate your {test:6, test2: 7}.

Try the following code. It looks plausible to me:

    Sub Post()
    
    Dim URL As String, JSONString As String, objHTTP as Object
    
        Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
        URL = "http://localhost:3000/test"
        objHTTP.Open "POST", URL, False
        objHTTP.setRequestHeader "Content-type", "application/json"
        objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    
        JSONString = "{""test"": 6,""test2"":7}"
    
        objHTTP.Send JSONString
    
    End Sub
Community
  • 1
  • 1
agustin
  • 1,311
  • 20
  • 42
  • 1
    Thanks! this did eventually solve the problem. Before this though I had to change the content-type header to application/json and add a content length filed to get rid of a 400 error. – mattc19 Nov 14 '15 at 19:28
0

If anyone else is struggling with this for POSTS, where your body isn't being sent at all, specifically when using a variable instead of literal text at the objHTTP.Send line, this worked for me, after struggling with it for hours, despite declaring my variables as strings:

    objHTTP.Send CStr(YourVariable)

This did NOT work for me, as I've seen posted elsewhere:

    objHTTP.Send CStr(YourVariable, vbFromUnicode)
Montclair
  • 81
  • 1
  • 10