(Excel) Xero Get Invoices

Get Xero company invoices.

For more information, see https://developer.xero.com/documentation/api/invoices

Download Excel Class Modules Chilkat Excel Class Modules

' This example requires the Chilkat API to have been previously unlocked.
' See Global Unlock Sample for sample code.

Dim http As Chilkat.Http
Set http = Chilkat.NewHttp

Dim jsonToken As Chilkat.JsonObject
Set jsonToken = Chilkat.NewJsonObject

success = jsonToken.LoadFile("qa_data/tokens/xero-access-token.json")
If (success = False) Then
    Debug.Print jsonToken.LastErrorText
    Exit Sub
End If

http.AuthToken = jsonToken.StringOf("access_token")

' Replace the value here with an actual tenant ID obtained from this example:
' Get Xero Tenant IDs
http.SetRequestHeader "Xero-tenant-id","83299b9e-5747-4a14-a18a-a6c94f824eb7"

http.Accept = "application/json"


Set resp = http.QuickRequest("GET","https://api.xero.com/api.xro/2.0/Invoices")
If (http.LastMethodSuccess <> True) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

Debug.Print "Response Status Code: "; resp.StatusCode

Dim jsonResponse As Chilkat.JsonObject
Set jsonResponse = Chilkat.NewJsonObject
Dim success As Boolean
success = jsonResponse.Load(resp.BodyStr)
jsonResponse.EmitCompact = False
Debug.Print jsonResponse.Emit()

If (resp.StatusCode <> 200) Then
    Debug.Print "Failed."

    Exit Sub
End If

' Sample output...
' (See the parsing code below..)
' 
' Use the this online tool to generate parsing code from sample JSON: 
' Generate Parsing Code from JSON

' {
'   "Id": "bda1f62f-0d63-4178-8d54-e91fd226987a",
'   "Status": "OK",
'   "ProviderName": "Chilkat2222",
'   "DateTimeUTC": "\/Date(1587210234493)\/",
'   "Invoices": [
'     {
'       "Type": "ACCPAY",
'       "InvoiceID": "0032f627-3156-4d30-9b1c-4d3b994dc921",
'       "InvoiceNumber": "9871",
'       "Reference": "",
'       "Payments": [
'         {
'           "PaymentID": "22974891-3689-4694-9ee7-fd2ba917af55",
'           "Date": "\/Date(1579737600000+0000)\/",
'           "Amount": 148.50,
'           "Reference": "Chq 409",
'           "HasAccount": false,
'           "HasValidationErrors": false
'         }
'       ],
'       "CreditNotes": [
'       ],
'       "Prepayments": [
'       ],
'       "Overpayments": [
'       ],
'       "AmountDue": 0.00,
'       "AmountPaid": 148.50,
'       "AmountCredited": 0.00,
'       "IsDiscounted": false,
'       "HasAttachments": false,
'       "HasErrors": false,
'       "Contact": {
'         "ContactID": "d6a384fb-f46f-41a3-8ac7-b7bc9e0b5efa",
'         "Name": "Melrose Parking",
'         "Addresses": [
'         ],
'         "Phones": [
'         ],
'         "ContactGroups": [
'         ],
'         "ContactPersons": [
'         ],
'         "HasValidationErrors": false
'       },
'       "DateString": "2020-01-15T00:00:00",
'       "Date": "\/Date(1579046400000+0000)\/",
'       "DueDateString": "2020-01-24T00:00:00",
'       "DueDate": "\/Date(1579824000000+0000)\/",
'       "Status": "PAID",
'       "LineAmountTypes": "Exclusive",
'       "LineItems": [
'       ],
'       "SubTotal": 135.00,
'       "TotalTax": 13.50,
'       "Total": 148.50,
'       "UpdatedDateUTC": "\/Date(1221560931500+0000)\/",
'       "CurrencyCode": "AUD",
'       "FullyPaidOnDate": "\/Date(1579737600000+0000)\/"
'     },
'     {
'       "Type": "ACCPAY",
'       "InvoiceID": "673dd7cc-beb7-4697-83d4-0c47cb400cc2",
'       "InvoiceNumber": "",
'       "Reference": "",
'       "Payments": [
'         {
'           "PaymentID": "4d06f609-5200-4364-9c8b-d4379a945252",
'           "Date": "\/Date(1580688000000+0000)\/",
'           "Amount": 974.60,
'           "Reference": "DD # 96013",
'           "HasAccount": false,
'           "HasValidationErrors": false
'         }
'       ],
'       "CreditNotes": [
'         {
'           "CreditNoteID": "7df8949c-b71f-40c0-bbcf-39f2f450f286",
'           "CreditNoteNumber": "03391",
'           "ID": "7df8949c-b71f-40c0-bbcf-39f2f450f286",
'           "HasErrors": false,
'           "AppliedAmount": 218.90,
'           "DateString": "2020-01-29T00:00:00",
'           "Date": "\/Date(1580256000000+0000)\/",
'           "LineItems": [
'           ],
'           "Total": 218.90
'         }
'       ],
'       "Prepayments": [
'       ],
'       "Overpayments": [
'       ],
'       "AmountDue": 0.00,
'       "AmountPaid": 974.60,
'       "AmountCredited": 218.90,
'       "IsDiscounted": false,
'       "HasAttachments": false,
'       "HasErrors": false,
'       "Contact": {
'         "ContactID": "d0cd2c4f-18a0-4f7c-a32a-2db00f29d298",
'         "Name": "PC Complete",
'         "Addresses": [
'         ],
'         "Phones": [
'         ],
'         "ContactGroups": [
'         ],
'         "ContactPersons": [
'         ],
'         "HasValidationErrors": false
'       },
'       "DateString": "2020-01-28T00:00:00",
'       "Date": "\/Date(1580169600000+0000)\/",
'       "DueDateString": "2020-02-04T00:00:00",
'       "DueDate": "\/Date(1580774400000+0000)\/",
'       "Status": "PAID",
'       "LineAmountTypes": "Exclusive",
'       "LineItems": [
'       ],
'       "SubTotal": 1085.00,
'       "TotalTax": 108.50,
'       "Total": 1193.50,
'       "UpdatedDateUTC": "\/Date(1221561913790+0000)\/",
'       "CurrencyCode": "AUD",
'       "FullyPaidOnDate": "\/Date(1580688000000+0000)\/"
'     },
'     {
'       "Type": "ACCPAY",
'       "InvoiceID": "c12aff7e-12bf-4185-8702-460929f19674",
'       "InvoiceNumber": "",
'       "Reference": "",
'       "Payments": [
'       ],
'       "CreditNotes": [
'       ],
'       "Prepayments": [
'       ],
'       "Overpayments": [
'       ],
'       "AmountDue": 2166.99,
'       "AmountPaid": 0.00,
'       "AmountCredited": 0.00,
'       "CurrencyRate": 1.000000,
'       "IsDiscounted": false,
'       "HasAttachments": false,
'       "HasErrors": false,
'       "Contact": {
'         "ContactID": "d0cd2c4f-18a0-4f7c-a32a-2db00f29d298",
'         "Name": "PC Complete",
'         "Addresses": [
'         ],
'         "Phones": [
'         ],
'         "ContactGroups": [
'         ],
'         "ContactPersons": [
'         ],
'         "HasValidationErrors": false
'       },
'       "DateString": "2020-04-10T00:00:00",
'       "Date": "\/Date(1586476800000+0000)\/",
'       "DueDateString": "2020-05-05T00:00:00",
'       "DueDate": "\/Date(1588636800000+0000)\/",
'       "Status": "AUTHORISED",
'       "LineAmountTypes": "Exclusive",
'       "LineItems": [
'       ],
'       "SubTotal": 1969.99,
'       "TotalTax": 197.00,
'       "Total": 2166.99,
'       "UpdatedDateUTC": "\/Date(1497965301980+0000)\/",
'       "CurrencyCode": "AUD"
'     }
'   ]
' }
' 


Id = jsonResponse.StringOf("Id")

Status = jsonResponse.StringOf("Status")

ProviderName = jsonResponse.StringOf("ProviderName")

DateTimeUTC = jsonResponse.StringOf("DateTimeUTC")

i = 0

count_i = jsonResponse.SizeOfArray("Invoices")
Do While i < count_i
    jsonResponse.I = i
    Type = jsonResponse.StringOf("Invoices[i].Type")
    InvoiceID = jsonResponse.StringOf("Invoices[i].InvoiceID")
    InvoiceNumber = jsonResponse.StringOf("Invoices[i].InvoiceNumber")
    Reference = jsonResponse.StringOf("Invoices[i].Reference")
    AmountDue = jsonResponse.StringOf("Invoices[i].AmountDue")
    AmountPaid = jsonResponse.StringOf("Invoices[i].AmountPaid")
    AmountCredited = jsonResponse.StringOf("Invoices[i].AmountCredited")
    IsDiscounted = jsonResponse.BoolOf("Invoices[i].IsDiscounted")
    HasAttachments = jsonResponse.BoolOf("Invoices[i].HasAttachments")
    HasErrors = jsonResponse.BoolOf("Invoices[i].HasErrors")
    ContactContactID = jsonResponse.StringOf("Invoices[i].Contact.ContactID")
    ContactName = jsonResponse.StringOf("Invoices[i].Contact.Name")
    ContactHasValidationErrors = jsonResponse.BoolOf("Invoices[i].Contact.HasValidationErrors")
    DateString = jsonResponse.StringOf("Invoices[i].DateString")
    Date = jsonResponse.StringOf("Invoices[i].Date")
    DueDateString = jsonResponse.StringOf("Invoices[i].DueDateString")
    DueDate = jsonResponse.StringOf("Invoices[i].DueDate")
    Status = jsonResponse.StringOf("Invoices[i].Status")
    LineAmountTypes = jsonResponse.StringOf("Invoices[i].LineAmountTypes")
    SubTotal = jsonResponse.StringOf("Invoices[i].SubTotal")
    TotalTax = jsonResponse.StringOf("Invoices[i].TotalTax")
    Total = jsonResponse.StringOf("Invoices[i].Total")
    UpdatedDateUTC = jsonResponse.StringOf("Invoices[i].UpdatedDateUTC")
    CurrencyCode = jsonResponse.StringOf("Invoices[i].CurrencyCode")
    FullyPaidOnDate = jsonResponse.StringOf("Invoices[i].FullyPaidOnDate")
    CurrencyRate = jsonResponse.StringOf("Invoices[i].CurrencyRate")
    j = 0
    count_j = jsonResponse.SizeOfArray("Invoices[i].Payments")
    Do While j < count_j
        jsonResponse.J = j
        PaymentID = jsonResponse.StringOf("Invoices[i].Payments[j].PaymentID")
        Date = jsonResponse.StringOf("Invoices[i].Payments[j].Date")
        Amount = jsonResponse.StringOf("Invoices[i].Payments[j].Amount")
        Reference = jsonResponse.StringOf("Invoices[i].Payments[j].Reference")
        HasAccount = jsonResponse.BoolOf("Invoices[i].Payments[j].HasAccount")
        HasValidationErrors = jsonResponse.BoolOf("Invoices[i].Payments[j].HasValidationErrors")
        j = j + 1
    Loop
    j = 0
    count_j = jsonResponse.SizeOfArray("Invoices[i].CreditNotes")
    Do While j < count_j
        jsonResponse.J = j
        CreditNoteID = jsonResponse.StringOf("Invoices[i].CreditNotes[j].CreditNoteID")
        CreditNoteNumber = jsonResponse.StringOf("Invoices[i].CreditNotes[j].CreditNoteNumber")
        ID = jsonResponse.StringOf("Invoices[i].CreditNotes[j].ID")
        HasErrors = jsonResponse.BoolOf("Invoices[i].CreditNotes[j].HasErrors")
        AppliedAmount = jsonResponse.StringOf("Invoices[i].CreditNotes[j].AppliedAmount")
        DateString = jsonResponse.StringOf("Invoices[i].CreditNotes[j].DateString")
        Date = jsonResponse.StringOf("Invoices[i].CreditNotes[j].Date")
        Total = jsonResponse.StringOf("Invoices[i].CreditNotes[j].Total")
        k = 0
        count_k = jsonResponse.SizeOfArray("Invoices[i].CreditNotes[j].LineItems")
        Do While k < count_k
            jsonResponse.K = k
            k = k + 1
        Loop
        j = j + 1
    Loop
    j = 0
    count_j = jsonResponse.SizeOfArray("Invoices[i].Prepayments")
    Do While j < count_j
        jsonResponse.J = j
        j = j + 1
    Loop
    j = 0
    count_j = jsonResponse.SizeOfArray("Invoices[i].Overpayments")
    Do While j < count_j
        jsonResponse.J = j
        j = j + 1
    Loop
    j = 0
    count_j = jsonResponse.SizeOfArray("Invoices[i].Contact.Addresses")
    Do While j < count_j
        jsonResponse.J = j
        j = j + 1
    Loop
    j = 0
    count_j = jsonResponse.SizeOfArray("Invoices[i].Contact.Phones")
    Do While j < count_j
        jsonResponse.J = j
        j = j + 1
    Loop
    j = 0
    count_j = jsonResponse.SizeOfArray("Invoices[i].Contact.ContactGroups")
    Do While j < count_j
        jsonResponse.J = j
        j = j + 1
    Loop
    j = 0
    count_j = jsonResponse.SizeOfArray("Invoices[i].Contact.ContactPersons")
    Do While j < count_j
        jsonResponse.J = j
        j = j + 1
    Loop
    j = 0
    count_j = jsonResponse.SizeOfArray("Invoices[i].LineItems")
    Do While j < count_j
        jsonResponse.J = j
        j = j + 1
    Loop
    i = i + 1
Loop