Excel VBA API POST JSON conversion
Results 1 to 2 of 2

Thread: Excel VBA API POST JSON conversion
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2018
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Excel VBA API POST JSON conversion

    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

    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

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Post Thanks / Like
    8 Post(s)
    2 Thread(s)

    Default Re: Excel VBA API POST JSON conversion

    The documentation is a little poor. Try changing the Open to specify a tracking number like this:

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts