VBA Code to POST each line as a JSON to API

Razr

New Member
Joined
Jan 13, 2009
Messages
35
Dear Members,

I have the following 2 VBA codes and I would like to make them into one.

What I want to accomplish is post each line as a separate json post request and parse the response json in each line as a new column.

Can you please help me.

The code now export all lines in one json cell. I want separate json for each line.

Sample Data below

Recipient_NameRecipient_AddressAPI Response (Voucher Nr)
John John112 Test Rd700041414141


VBA Code:
Public Sub exceltonestedjson()
Dim rng As Range, items As New Collection, myitem As New Dictionary, subitem As New Dictionary, i As Integer, cell As Variant
Set rng = Range("A2", Cells(Rows.Count, 1).End(xlUp))
i = 0
For Each cell In rng
Debug.Print (cell.Value)
subitem("User_ID") = "???"
subitem("User_Password") = "???"
subitem("Pickup_Date") = Now()
subitem("Recipient_Name") = cell.Value
subitem("Recipient_Address") = cell.Offset(0, 3).Value
items.Add myitem
Set subitem = Nothing
Set myitem = Nothing
i = i + 1
Next
Sheets(1).Range("A30").Value = ConvertToJson(items, Whitespace:=2)
  Columns("A:A").Select
    Selection.Replace What:="[", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  Columns("A:A").Select
    Selection.Replace What:="]", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
     
      
    Dim objHTTP As Object
    Dim Json As String
    Json = Range("A30") 

    Dim result As String

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    Url = "https://webservices.net/example"
    objHTTP.Open "POST", Url, False

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

   'Some simple debugging
   Range("B30").Value = result

   Set objHTTP = Nothing


        
        
End Sub
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,114,360
Messages
5,547,469
Members
410,797
Latest member
mlfuson
Top