title: VBA POST json to API
body: <p>I am trying to write VBA to post json to an api and parse the results into a worksheet. I can generate the JSON and am confident I can parse the result into what I need.</p>
  
  <p>I know there are online tools to convert json to vba and back and browser add ins to post requests but I am the only one in the office that can do this so if I'm sick or on leave I would like to automate it. To do that I need to send the json and maybe store the response so I can parse it. </p>
  
  <p>I'm new to coding so posting a request like this is over my head.
  So far I have the following code to write the json. I would appreciate any help in getting me started. If needed I can post a sample of the json or the api I would like to post it to.</p>
  
  <p>Apologies for the poor code I know I can improve it but want to get the json response as I think it will be the most challenging part.</p>
  
  <p>EDIT Have made some progress. Can now send a JSON string to the URL and get the response. However it is always returning a failure:</p>
  
  <p>"{
    ""message"": ""An error has occurred.""
  }"</p>
  
  <p>If I manually send the json with httpRequestor the result is returned correctly.
  This seems to suggest that somewhere in the code the JSON is getting mixed up or modified somehow when it is being posted.</p>
  
  <p>Updated code below. (Have removed any reference to actual data)</p>
  
  <p>EDIT 2 fixed and working.
  Removed quotes from </p>
  
  <p>objHTTP.send ("Json")</p>
  
  <pre><code>    Private Sub CommandButton21_Click()
  
  Dim h_1 As String
  Dim h_2 As String
  
  h_1 = Range("A1")
  h_2 = Range("B1")
  h_3 = Range("C1")
  h_4 = Range("D1")
  h_5 = Range("E1")
  h_6 = Range("F1")
  
  sv_1 = 2
  sv_2 = 2
  sv_3 = 2
  sv_4 = 2
  sv_5 = 2
  sv_6 = 2
  
  For f = 15 To 21
  v_1 = Range("A" &amp; sv_1)
  v_2 = Range("B" &amp; sv_2)
  v_3 = Range("C" &amp; sv_3)
  v_4 = Range("D" &amp; sv_4)
  v_5 = Range("E" &amp; sv_5)
  v_6 = Range("F" &amp; sv_6)
  y = "[{""" &amp; h_1 &amp; """:""" &amp; v_1 &amp; """,""" &amp; h_2 &amp; """:""" &amp; v_2 &amp; """,""" &amp; h_3 &amp; """:""" &amp; v_3 &amp; """,""" &amp; h_4 &amp; """:""" &amp; v_4 &amp; """,""" &amp; h_5 &amp; """:""" &amp; v_5 &amp; """,""" &amp; h_6 &amp; """:""" &amp; v_6 &amp; """ }]"
  
  Range("A" &amp; f).Value = y
  sv_1 = sv_1 + 1
  sv_2 = sv_2 + 1
  sv_3 = sv_3 + 1
  sv_4 = sv_4 + 1
  sv_5 = sv_5 + 1
  sv_6 = sv_6 + 1
  Next f
  
  
  
  
  
      Dim objHTTP As Object
      Dim Json As String
      Json = Range("A15")
      Dim result As String
      'Set objIE = CreateObject("InternetExplorer.Application") ' Don't think this is needed
      'objIE.navigate "about:blank" ' Don't think this is needed
      'objIE.Visible = False ' Don't think this is needed
      Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
      URl = "http://myApi/iSendJsonTo"
      objHTTP.Open "POST", URl, False
      'objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
     objHTTP.setRequestHeader "Content-type", "application/json"
     objHTTP.send ("Json")
     result = objHTTP.responseText
     'objIE.document.Write result ' Don't think this is needed
  
     'Some simple debugging
     Range("A25").Value = result
     Range("A26").Value = Json
  
  
     Set objHTTP = Nothing
  </code></pre>