VBA Code to POST each line as a JSON to API

[Razr]

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_Name	Recipient_Address	API Response (Voucher Nr)
John John	112 Test Rd	700041414141

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

 

[Razr]

Bump