Run JSON in Excel

[Shaiba]

URL: https://ws.aramex.net/ShippingAPI.V2/Shipping/Service_1_0.svc/json/PrintLabel

{

"ClientInfo": {

"UserName": "register in aramex.com as personal and put email here ",

"Password": " register in aramex.com as personal and put password here ",

"Version": "v1",

"AccountNumber": "put your account number",

"AccountPin": "put your pin number",

"AccountEntity": "RUH",

"AccountCountryCode": "SA",

"Source": 24

},

"LabelInfo": {

"ReportID": 9201,

"ReportType": "URL"

},

"OriginEntity": "RUH",

"ProductGroup": "DOM",

"ShipmentNumber": "Varible number-in cell",

"Transaction": {

"Reference1": "",

"Reference2": "",

"Reference3": "",

"Reference4": "",

"Reference5": ""

}

}







#Response







{

"Transaction": {

"Reference1": "",

"Reference2": "",

"Reference3": "",

"Reference4": "",

"Reference5": ""

},

"Notifications": [],

"HasErrors": false,

"ShipmentNumber": "47205774836",

"ShipmentLabel": {

"LabelURL": "https://ws.aramex.net/content/rpt_cache/1ef0c230cc60438e92cadbb1ca0327b5.pdf",

"LabelFileContents": []

}

}

 

[Dan_W]

No one will be able to help you if you don't tell us what the problem is.

 

[Shaiba]

No one will be able to help you if you don't tell us what the problem is.

how to send this req and print the label in VBA excel​

 

[Dan_W]

[No comment]

 

[Shaiba]

[No comment]

Mr. Dan_W please accept my apologies, English is not my first lang not even a second, thank you for your patience, am trying to run this JSON API in excel to get different results every time I insert a new shipment number, and couldn't do it because as you see there is a lot of variants I need to refer them to cells in the workbook that could change each time I run the code. sorry again this is google translation: -)

 

[Dan_W]

Thank you for the follow up message - it has helped clarify what you need help with, and thank you kindly for the apology. I understand the difficulties in communicating in another language, so hopeully Google Translate will be able to help.

VBA does not natively support JSON, so my recommendation is to download a free open-source library from Github - VBA-JSON. The library comprises a number of files, but the key file is JsonConverter.bas. Once you have imported the file jsonConverter.bas, the following code should help you in procesing the JSON results. I have written one subroutine ("Test") and one function ("RetrieveDetails"). I have also written two User Defined Types, but the main one is called ShipmentDetails. When you pass your JSON code through to the function, it will return a data type ShipmentDetails - I have created it based on the available information in the JSON, so hopefully it captures all the key data. The Test subroutine demonstrates how the code should work.

I recommend copying the code below into a new standard module.

Please let me know if you have any difficulty with the code.

Type ShipmentLabelDetails
    LabelFileContents As Variant
    LabelURL As String
End Type
Type ShipmentDetails
    HasErrors As Boolean
    ShipmentNumber As String
    Transaction As Variant
    ShipmentLabel As ShipmentLabelDetails
    Notifications As Variant
End Type

Sub TestCode()
    Dim TestCode As String
    Dim Shipment As ShipmentDetails
    
    TestCode = "{""Transaction"": {""Reference1"": """", ""Reference2"": """", ""Reference3"": """", ""Reference4"": """", ""Reference5"": """"}, ""Notifications"": [], ""HasErrors"": false, ""ShipmentNumber"": ""47205774836"", ""ShipmentLabel"": {""LabelURL"": ""https://ws.aramex.net/content/rpt_cache/1ef0c230cc60438e92cadbb1ca0327b5.pdf"", ""LabelFileContents"": []}}"

    Shipment = RetrieveResults(TestCode)
    
    Dim Statement As String
    Statement = "Shipment Number:   " & Shipment.ShipmentNumber & vbNewLine
    Statement = Statement & "Label URL:    " & Shipment.ShipmentLabel.LabelURL
    
    MsgBox Statement
    
End Sub

Function RetrieveResults(JSONCode As String) As ShipmentDetails
    Dim JSON As Object
     Set JSON = JsonConverter.ParseJson(JSONCode)

    With RetrieveResults
        .HasErrors = JSON("HasErrors")
        .ShipmentNumber = JSON("ShipmentNumber")
        Set .ShipmentLabel.LabelFileContents = JSON("ShipmentLabel")("LabelFileContents")
        .ShipmentLabel.LabelURL = JSON("ShipmentLabel")("LabelURL")
        Set .Transaction = JSON("Transaction")
        Set .Notifications = JSON("Notifications")
    End With

End Function