VBA POST json to API

Question

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.

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.

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.

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.

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:

"{ ""message"": ""An error has occurred."" }"

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.

Updated code below. (Have removed any reference to actual data)

EDIT 2 fixed and working. Removed quotes from

objHTTP.send ("Json")

    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" & sv_1)
v_2 = Range("B" & sv_2)
v_3 = Range("C" & sv_3)
v_4 = Range("D" & sv_4)
v_5 = Range("E" & sv_5)
v_6 = Range("F" & sv_6)
y = "[{""" & h_1 & """:""" & v_1 & """,""" & h_2 & """:""" & v_2 & """,""" & h_3 & """:""" & v_3 & """,""" & h_4 & """:""" & v_4 & """,""" & h_5 & """:""" & v_5 & """,""" & h_6 & """:""" & v_6 & """ }]"

Range("A" & 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

Solution

Here is the code that is sending the JSON, cleaned up a little.

    Dim objHTTP As Object
    Dim Json As String
    Json = Range("A15") 'here I am pulling in an existing json string to test it. String is created in other VBA code

    Dim result As String

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URl = "http://myApi/iSendJsonto/"
    objHTTP.Open "POST", URl, False

   objHTTP.setRequestHeader "Content-type", "application/json"
   objHTTP.send (Json)
   result = objHTTP.responseText

   'Some simple debugging
   Range("A25").Value = result
   Range("A26").Value = Json


   Set objHTTP = Nothing

Comments

[OfficeTricks.Com]

How to do the same with InternetExplorer.Application object?