[VB6/VBA] JSON parsing to built-in VBA.Collections with JSON Path support

[wqweto]

Here is some sample code how to populate your data using JSON Path syntax

Option Explicit

Private Sub Form_Load()
    Dim oJson As Object
    Dim oItem As Object
    
    JsonValue(oJson, "$.ApplicationIdentifier") = "ERP"
    JsonValue(oJson, "$.InvoiceNum") = "6"
    JsonValue(oJson, "$.InvoiceDateTime") = "2023-01-01 12:00:00"
    JsonValue(oJson, "$.CustomerName") = "TEST"
    JsonValue(oJson, "$.StandardRateSales") = 400
    JsonValue(oJson, "$.StandardRateVat") = 60
    JsonValue(oJson, "$.StandardVatRate") = 15
    JsonValue(oJson, "$.NetAmnt") = 400
    JsonValue(oJson, "$.TotalVatAmnt") = 60
    JsonValue(oJson, "$.NetAmntWithVat") = 460
    JsonValue(oJson, "$.InvoiceLine[0].Quantity") = 1
    JsonValue(oJson, "$.InvoiceLine[0].LineNetAmnt") = 300
    JsonValue(oJson, "$.InvoiceLine[0].LineVatAmnt") = 45
    JsonValue(oJson, "$.InvoiceLine[0].LineNetAmntWithVat") = 345
    JsonValue(oJson, "$.InvoiceLine[0].ItemName") = "CONSULTANT 1"
    JsonValue(oJson, "$.InvoiceLine[0].ItemCode") = "RC01"
    JsonValue(oJson, "$.InvoiceLine[0].VatRate") = 15
    JsonValue(oJson, "$.InvoiceLine[0].ItemPrice") = 300
    JsonValue(oJson, "$.InvoiceLine[1].Quantity") = 1
    JsonValue(oJson, "$.InvoiceLine[1].LineNetAmnt") = 100
    JsonValue(oJson, "$.InvoiceLine[1].LineVatAmnt") = 15
    JsonValue(oJson, "$.InvoiceLine[1].LineNetAmntWithVat") = 115
    JsonValue(oJson, "$.InvoiceLine[1].ItemName") = "CONSULTANT 2"
    JsonValue(oJson, "$.InvoiceLine[1].ItemCode") = "RC02"
    JsonValue(oJson, "$.InvoiceLine[1].VatRate") = 15
    JsonValue(oJson, "$.InvoiceLine[1].ItemPrice") = 100
    Debug.Print JsonDump(oJson)
    
    '--- start all over
    Set oJson = Nothing
    JsonValue(oJson, "$.ApplicationIdentifier") = "ERP"
    JsonValue(oJson, "$.InvoiceNum") = "6"
    JsonValue(oJson, "$.InvoiceDateTime") = "2023-01-01 12:00:00"
    JsonValue(oJson, "$.CustomerName") = "TEST"
    JsonValue(oJson, "$.StandardRateSales") = 400
    JsonValue(oJson, "$.StandardRateVat") = 60
    JsonValue(oJson, "$.StandardVatRate") = 15
    JsonValue(oJson, "$.NetAmnt") = 400
    JsonValue(oJson, "$.TotalVatAmnt") = 60
    JsonValue(oJson, "$.NetAmntWithVat") = 460
    
    '--- prepare first line item
    Set oItem = Nothing
    JsonValue(oItem, "$.Quantity") = 1
    JsonValue(oItem, "$.LineNetAmnt") = 300
    JsonValue(oItem, "$.LineVatAmnt") = 45
    JsonValue(oItem, "$.LineNetAmntWithVat") = 345
    JsonValue(oItem, "$.ItemName") = "CONSULTANT 1"
    JsonValue(oItem, "$.ItemCode") = "RC01"
    JsonValue(oItem, "$.VatRate") = 15
    JsonValue(oItem, "$.ItemPrice") = 300
    JsonValue(oJson, "$.InvoiceLine[-1]") = oItem   '--- use -1 index to append to array
    
    '--- prepare second line item
    Set oItem = Nothing
    JsonValue(oItem, "$.Quantity") = 1
    JsonValue(oItem, "$.LineNetAmnt") = 100
    JsonValue(oItem, "$.LineVatAmnt") = 15
    JsonValue(oItem, "$.LineNetAmntWithVat") = 115
    JsonValue(oItem, "$.ItemName") = "CONSULTANT 2"
    JsonValue(oItem, "$.ItemCode") = "RC02"
    JsonValue(oItem, "$.VatRate") = 15
    JsonValue(oItem, "$.ItemPrice") = 100
    JsonValue(oJson, "$.InvoiceLine[-1]") = oItem   '--- use -1 index to append to array
    Debug.Print JsonDump(oJson)
End Sub

cheers,
</wqw>