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

Describe the actions you are doing manually, and which you want to automate. — omegastripes
– omegastripes, Commented Mar 12, 2015 at 20:23
Converting the data to json and posting it to a URL using httpRequester (Firefox add in). Copying the response json and converting it back to excel then pasting the data back into excel. I'm using convertcsv.com to convert the data back and forth. What I'd like to do is use vba to parse the data rather than copying it to convertcsv.com which I can do with string manipulation and use excel to send the json and get the response json which seems more difficult. — deving
– deving, Commented Mar 13, 2015 at 9:05
Found some good code at stackoverflow.com/questions/23663149/… was able to send the json but result is incorrect. Edited question with more info. — deving
– deving, Commented Mar 13, 2015 at 14:53

deving · Accepted Answer · 2015-03-13 15:08:40Z

7

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

edited Mar 13, 2015 at 15:08

answered Mar 13, 2015 at 14:59

deving

911 gold badge1 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OfficeTricks.Com Over a year ago

How to do the same with InternetExplorer.Application object?

Collectives™ on Stack Overflow

VBA POST json to API

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related