Excel VBA API POST JSON conversion

smithc2005

New Member
Joined
May 11, 2018
Messages
2
I have a spreadsheet with tracking information on it that I am trying to send through a API to create a tracking event. The issue I have is making sure my body is in JSON format. I have managed to parse my excel cells to JSON but now I need to know how to actually save this as a string instead of out to a file so I can send it.
The API is https://docs.aftership.com/api/4/overview

Code:
Sub PostTrack()    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    With xmlHttp
    .Open "POST", "https://api.aftership.com/v4/trackings/", False
    .setRequestHeader "aftership-api-key", "MYAPIKEY"
    .setRequestHeader "Content-Type", "application/json"


        'Convert Excel to JSON


            Dim excelRange As Range
            Dim jsonItems As New Collection
            Dim jsonDictionary As New Dictionary
            Dim i As Long
            Dim cell As Variant
    
    
            Set excelRange = Cells(1, 1).CurrentRegion


            For i = 1 To excelRange.Rows.Count
            jsonDictionary("title") = Cells(i, 1)
            jsonDictionary("tracking_number") = Cells(i, 2)
    


            jsonItems.Add jsonDictionary
            Set jsonDictionary = Nothing
            Next i


            MsgBox JsonConverter.ConvertToJson(jsonItems, Whitespace:=3)
    
            Dim jsonFileObject As New FileSystemObject
            Dim jsonFileExport As TextStream
    
            Set jsonFileExport = jsonFileObject.CreateTextFile("C:\Users\csmit\Desktop\jsonExample.json", True)
            jsonFileExport.WriteLine (JsonConverter.ConvertToJson(jsonItems, Whitespace:=3))
    
'Send JSON Body to API
'????????
    
End With


End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
The documentation is a little poor. Try changing the Open to specify a tracking number like this:

Code:
Dim TrackingNumber As String
TrackingNumber = "12345"
.Open "POST", "https://api.aftership.com/v4/trackings&tracking_number=" & TrackingNumber, False
and send the request using:

Code:
Dim JSONstring As String
JSONstring =  JsonConverter.ConvertToJson(jsonItems, Whitespace:=3)
.Send (JSONstring)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top