looking to see if there is a way i can extract all the contacts out of xero into excel via vba and an api

Question

have a client in south africa that uses xero as their accounting software. I have written a pricing sheet that is rather complicated but it relies on having an up to date contact list from xero. Right now we are manually extracting the contacts and pasting them into my excel sheet. I want to know if there is an API that i can use from excel vba to pull the full list of contacts from Xero.

Answer 1

The list of currently-available APIs is here: https://developer.xero.com/documentation/libraries/overview

I can't see one that you could use directly from VBA, but could you write something in "proper" VB.Net to interface between Xero and Excel? There is a .net SDK that makes it quite straightforward to retrieve contact details.

Answer 2

Rather than using VBA, try the more recent excel 'Get Data' functionality which uses the older Power Query, similar to queries in Power BI.

You should be able to use something along the lines of the following:

let
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Pages = 1, Counter=0], // Start Value
        each  Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen
        each [ WebCall = Json.Document(Web.Contents("INSERT XERO API CALL", [Query=[page=Text.From([Pages])]])),// retrieve results per call
                Pages = [Pages]+1,
                Counter = [Counter]+1,// internal counter
                Table = Table.FromRecords(WebCall[orders])// steps of your further query
                      ] 

),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
#"Converted to Table"

The above will also paginate the response, but you will have to play with the Oauth2 refrsh tokens and adapt it for Xero Api call. Dig on the internet for more details.