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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Watch MrExcel Video

Forum statistics

Threads
1,114,648
Messages
5,549,176
Members
410,903
Latest member
natesreich
Top