Xero - beautiful accounting software

Xero Developer Help Center

Xero Developer Community

Community > Wrapper libraries >

Example Excel VBA Connection to Xero via OAuth

Started by Eliot Muir -   in Wrapper libraries

I just wrote an example Oauth 1.0 implementation in VBA in Excel 2016 on Windows which makes it possible for one to have a button that can call the Xero APIs. Noticed that there weren't any examples with VBA with Xero that I could find publicly.

It is a bit of quirky environment so I thought I would share what I did in case anyone else is trying to get data feeding directly into Excel from the Xero web APIs:

http://help.interfaceware.com/kb/writing-a-xero-adapter/5

It comes to a fairly large amount of code because VBA doesn't have a lot of built in features one takes for granted in many other environments.

I haven't gotten on to the next step of actually putting a JSON parser in there and putting the data into a spreadsheet. One step at a time...

Hope it's useful to someone :-)
Hi Eliot,

Thanks for writing this, I got it working in my environment pretty much as is. My only issue was with our proxy server.

Great code.
 

tim evans  

You're welcome. As it turned out finally we ended up deciding that it made a better approach create a SQL database to mirror the data from Xero into it for Invoices.

The problem is there isn't a Xero API call you can make which can give you all say the invoices - the problem is to get the invoice detail you have to call an API which gives you a page of invoices - I forget the exact number but it's like 500 at time. So you end up needing to loop through to get that data which would be really painful to code out in an environment like VBA.

I found it was much easier to do that in the context of another environment (in my case I used our own integration product Iguana).

From there it was much faster and more convenient to serve the data up to excel via our own webservices in a more convenient format.
 

Eliot Muir  

For the mechanism of dispatching code off to excel I ended up completely redoing my first implementation - it's helpful not to have to worry about OAuth in the context of Excel. Most members of my company have Macbooks so I ended up writing some cross platform VBA code which works under both windows and Mac (a little insane but it works really well!)

With that implementation I didn't use JSON. There is a JSON parser for VBA maintained by a guy in NZ of all places (I'm an ex NZ national myself). The problem though is it makes extensive use of native WIN32 calls - so it's not portable to the Mac version of office.

Instead I went with a simple custom comma delimited format for which it was simple enough to write a parser for (I don't like programming in VBA so I limit the amount I have to write).

With that implementation I paid a lot more attention to error handling and user feedback about the data download since it's a tool we're using with our sales team. I put a lot more attention into writing the VBA code optimally.

One trick for instance is to assemble the data you want to feed into an excel table into a variant matrix and then assign in one action to the table - it's a lot faster.

Other tricks are to resize the table ranges in one go.

The other big advantage of doing things this way is the ability to control permissions over the data much more strongly. The Xero permission system hasn't been terribly well thought out - it's a lot easier just to take control of that at a granular level.
 

Eliot Muir  

I find that once you do get the raw data into Excel that it's possible to build much richer and more convenient reports using Pivot tables that come out of Xero natively. Excel reporting rocks compared to what comes out of just about any off the shelf package.

Pivot tables make it super convenient to expand and collapse parts of the pivot table so it's much much faster to get a view of what line item contribute to a number. It's dead easy to filter out data by things like the quarter etc. and zero in what is happening. It does take effort though - we're doing it as part of a much larger project of cross-referencing financial data with our CRM, support desk etc.

I have my entire management team able to click a button and get a excel spreadsheet with key financial data they need, we're just in the process of rolling out similar data to the sales team. It's been very helpful so far.

If you go and look at this thread you can download the latest spreadsheet and have a look at the VBA code:

http://help.interfaceware.com/forums/topic/serving-live-data-into-excel
 

Eliot Muir  

Thanks for that, it's a useful sample to build my own.
I think I've found an alternative to your cmd/openssl calls: System.Security.Cryptography.HMACSHA1. Do you know if there is any problem with that?
 

Tim von Ahsen  

Nope there would be be no problem with using another cypher library - it's all standard. I was using a Mac so I didn't have access to as wide a variety of libraries from the operating system like you'd had on Windows.

Sorry for the slow reply - had to look up my Xero password...
 

Eliot Muir  

Thanks Elliot. I've got the Authentication working now. RSA-SHA1 (needed for Private apps) isn't as simple to use as the HMACSHA1 class, so I've made mine a Public app and used HMAC-SHA1. Next step is to download data and parse json, that should be easier than the authentication.
 

Tim von Ahsen  

Hi Eliot
Thank you for your post.
I have a small amount of VBA knowledge.
What I am trying to do is simply logon to Xero, to get the next invoice number https://go.xero.com/AccountsReceivable/Edit.aspx/
via VBA in excel 2016.

I wasn't quite sure how to follow your link, but if you could be so kinde, to place the VBA code in this post that I simply paste into the module.

I will then try using the logon credentials.

Thank you in Advance.

Eddie
 

Eddie Arida  

Hi Eddie,
If you're interested in a paid solution, I can offer Excel/Xero consulting through www.ableowl.com. I recently finished an Excel/VBA application that interfaces with Xero, so we now offer that service to other clients.
Tim
 

Tim von Ahsen  

Thank you Tim for your response,
I will surely pass it on to management your suggestion.
At first we were trying to get
enough votes to allow Xero to implement this:
https://community.xero.com/business/discussion/1771936
I guess it will not harm if we reach that vote count through clients that may be interested to vote.
Thank you.
Eddie
 

Eddie Arida  

Hi Tim,
I am interested in your VBA application. We need to pull Xero Project report data to excel. Let me know if you can help with the coding. Thanks.
 

Anna Schwartz (Admin)  

Hi Anna,
Sure. Send me an email at tahsen@ableowl.com and we can arrange an online meeting.
Tim
 

Tim von Ahsen