Xero - beautiful accounting software

Xero Developer Help Center

Xero Developer Community

Community > API Endpoints >

Microsoft Access VBA connection to Private App - Success

Started by Tapaha Robati -   in API Endpoints

I haven't been able to figure out where this could go, hopefully Admin you can move this to where applicable?

One of the more challenging things I've discovered is that there is limited support for writing VBA (I know, the latin of coding languages) in terms of writing the calls correctly to hit Xero endpoints. All the wrappers are in the key languages such as C#, python, java etc.

Basically, long story short...I'm not a programmer. But I've spent months figuring it out and can successfully call all the Accounting Endpoints via writing some VBA in to an Access form.

I'm still working on it, but the establishing of the connection is what seems to have been the universal problem. This part I've solved and am happy to provide help to anyone who needs it. For someone who understands VBA syntax...you will get it quite easily. I've done the trial and error for you. It's very painful.

I want to see if there is anyone else out there needing this? My end goal is to help people use these findings for building in to Excel or the like. Yes it could be other languages, but I started this for my dads small business. Why would I want to use a cannon to kill a mosquito?
Hi,

You need a oauth authentication library. Have a look at https://github.com/VBA-tools/VBA-Web/blob/master/authenticators/OAuth1Authenticator.cls

Kind Regards

Remember I consult on a per hour basis. Contact me if your need me to fix the problem. $45 per hour.

We can do a 30 min free consultation via Skype.

Henzard Kruger
Certified Advisor and Full Stack developer
Picahoo cc - 0711304241 - henzard@picahoo.co.za
Skype: henzard, Gtalk: henzardkruger@gmail.com
Quality is remembered long after the price is forgotten

I abide by the 3 message rule. After 3 messages we skype.

 

Henzard Kruger  

Hey Henzard

Cheers for the follow up.

I pulled this off without one but I half agree because I originally read all through the vba-tools to understand how they work. After seeing how complicated they were to someone not so super code savvy, I figured out I could slim down the entire process.

When you set up a set of certificates for the first time, you use OpenSSL. I just got familiar with the way it works. My solution doesn't use any libraries outside the standard included in office...but rather leans on the capability of OpenSSL for signing/authentication and then IXMLDocument etc. for the rest of the handling.

You may have misread here bro. I was actually offering to share what I've learned to anyone who needs it. Open source kind of thing. Anyone could take my learnings and do some crazy things with them I believe.

Tups
 

Tapaha Robati  

Hi,

Sorry was running through the community trying to help where I can. Maybe build a couple of concepts and put it on git. We do the same with asp. It would be excellent if we can get your code to do public apps with asp classic loads of people need it.

I am always up to collab on open source let me know where I can help.
 

Henzard Kruger  

Hi

I would be very interested in what ever help you can offer. I invoice monthly using an Access program I wrote myself which interfaces with ConnectWise. I currently export to CSV and import into Xero. It would great to fully automate it and upload the PDF invoices.

Any suggestions / code much appreciated

Simon
 

Simon Knight  

Hi,

I suggest you user a private application using https://github.com/XeroAPI/Xero-Net then connect to access using ADO.net and run the console application with the widows scheduler. This solution will be some work but at least it will be yours and no monthly fees.

Kind Regards

Remember I consult on a per hour basis. Contact me if your need me to fix the problem. $45 per hour.

We can do a 30 min free consultation via Skype.

Henzard Kruger
Certified Advisor and Full Stack developer
Picahoo cc - 0711304241 - henzard@picahoo.co.za
Skype: henzard, Gtalk: henzardkruger@gmail.com
Quality is remembered long after the price is forgotten

I abide by the 3 message rule. After 3 messages we skype.

 

Henzard Kruger  

Simon I'll clean the code up and dump it here bro. Haven't used it for a while because we found other business issues that require focus, however this will allow you to make a successful call and get an XML response from a few example endpoints. I'll leave the rest to you. It was always the connection part that was a problem to get past because of VBA limitations and Oauth.
 

Tapaha Robati  

Thank you very much, I look forward to it!
 

Simon Knight  

Hi Tapaha,

Around the same time you were working on this, I was as well, but you seem to have gotten further than I did.

I'm very experienced with Access and VBA and I was trying to employ the .net wrapper. I got as far as building a C# app that did connect to Xero and that was a step forward. And I could create a C# app that was Com enabled so it could be referenced in a VBA project.

BUT I couldn't get an app using the .net Xero wrapper to be com enabled as the .net wrapper isn't and I ran out of time trying to make that happen.

So I'd be very interested to compare notes and see if we can get a usable VBA interface to Xero. I'm sure there would be many others who would find it useful.

Kent
 

Kent Gorrell  

Hey Everyone

Here it is. I'm sorry it took so long to clean up...I have a day job haha.

I didn't know how else to share it so I created a Github to host it. Please...share it...help anyone else you can...pay it forward I guess

The assumptions about what you need are in there. If you have questions, hit us up on here and I'll try my best to answer.

https://github.com/TheMonkeysJandel/Xero-to-Access
 

Tapaha Robati  

WOW! That is impressive.

Just one form and one module, but I can only imagine how much time you put into this.
And how tenacious you must be to have achieved this.

Hopefully, I'll have enough time over the next few days to get a new Key and give it a tryout.

I'll let you know how I go.
 

Kent Gorrell  

Yes this is impressive !

You might consider posting this on the Utter Access forum - the first stop place, in my opinion, for Access Users/ Developers https://www.utteraccess.com/forum/
Congratulations on the effort you must have put in here.
 

Peter Kaye  

Cheers peeps...I like a challenge haha. Truth be told, I used the C# wrapper originally and just followed it line by line to try and mirror the same types of outputs in vba...not fun. Hence the reliance on openssl to make this work. If someone could figure out how to build that functionality directly in to this, it would be next level. That stumped me for months.
 

Tapaha Robati  

Oh how exciting, looks like a lot of work has gone into this, top work Tapaha!
 

Dean Graham  

I ran into a problem with an endless loop in TEXTFileReader as the signature file was empty.
The resolution was to copy the privatekey.pem file to /Xero-to-Access-master/keys/EF/
I couldn't see that it needed to be there in the notes.
Still working through the rest but its so great to have this as a starting point for my project.
 

Dean Graham  

Good pick up Graham. Yeah it’s not perfect...like I’ve said...I’m not a pro at this, just a persistent fulla. That loop you’re talking about I picked up when I moved it all to my work laptop as well.
 

Tapaha Robati  

Absolutely AMAZING - thank you SO much, Tapaha!! I have uploaded and attached a PDF to an existing invoice in Xero and it WORKS!!

I just hacked it into your code and have a world of niggles and work to do but as a principle its a FANTASTIC base to build on.

Thanks again and sorry it took me so long to respond (summon up the courage to start work on it!)
 

Simon Knight  

Excellent. I'm glad this is helping people!
 

Tapaha Robati  

@Tapaha, firstly thank you for creating this project, it is a great help! I do however need some assistance.
1. privatekey.pem - when i follow the xero dev page (https://developer.xero.com/documentation/api-guides/create-publicprivate-key) to create my priv/public key using openssl - where or how do i get the privatekey.pem file?
 

Johan Dekker  

Hey Johan....the keys by default land in the OpenSSL directory. BY default it installs on the Root C drive (system drive) and usually in the Bin folder I believe? I don't have it install on this machine I have with me unfortunately to confirm the exact name but yeah, just dig through the OpenSSL folder bro. They're in there.
 

Tapaha Robati  

Thank you Tapaha, i got it to work. This is great!
 

Johan Dekker  

Thanks Tapaha - there's so much misinformation out there about the Xero API and how to get to it, your example has helped me a great deal. Note for anyone looking, you need openssl on your machine to use the API if you have a private application. If you want to do this without openssl you need to have a public or published app. Valuable lesson learned!

I've taken cues from what you've done to make this work for me (using Access/VBA). I now need to integrate this further so that I can really use the APIs to their full potential.
 

John Crighton  

Brilliant. I'm glad this is helping people still.
 

Tapaha Robati  

Hi Tapaha, thank you for all your work on this.

Initially I was getting "Precondition failed", so I added the Registry key from here and some time later, after reloading MS Access and web browser and fiddling with my Xero account, it suddenly changed to a timestamp error. So I changed the Timezone_Offset to -1, for the UK on BST (I expect it will need to be 0 for GMT in a couple of weeks) and it worked.

Next I'll be writing the code to post sales invoices.
 

David Nye  

If you want to make sure you're always using UTC (Internet time), the following works for me:

https://excelribbon.tips.net/T012238_Automatically_Converting_to_GMT.html
 

John Crighton  

Thank you John, that got me on the right track. Since we just need the current date/time, I cobbled together a simpler version using just one Windows system call (to GetSystemTime). Posting invoices and updating contact details turns out to be very straightforward; it seems to be a well thought out and simple XML interface.
 

David Nye  

Agreed, once you have the XML in a recordset it's easy to navigate. Watch out for using the system time as things will come unstuck when the clocks change. I've been through that when calling secure URIs from VBA before!
 

John Crighton  

In the UK the system time appears to always be GMT, which seems to be what is required. We'll find out on 28th!
 

David Nye  

No, you're right: "SystemTime: Retrieves the current system date and time. The system time is expressed in Coordinated Universal Time (UTC)." - learn something new every day.
 

John Crighton  

Hi
I'm having problems with the signature file, which may be due to my openssl not creating the signature.txt. Can anyone point me in the right direction
cheers
 

Steve B  

Thanks for this. I'm very excited to try it out.
 

Liz Haskin  

Hi everyone.

If this has helped you at all and you've gone further with it than I did, please share it around. I'd love to revisit this little project because I'm a firm believer in open source and getting this to a point where anyone can pick it up and just run with it.
 

Tapaha Robati  

It definitely helped me enormously. I use it every month to batch upload our invoices into Xero. I took the code and created my own routine which uploads PDFs of our invoices and binds them to the associated invoice line (which I've already imported into Xero using CSV)
As a future expansion I plan to incorporate this code into our invoice system so that we create the invoice in Xero and upload the PDF at the point of creation but I haven't found the time to do this yet.
Thank you very much for all the hard work you put in so that I was able to do this! I'd have never got there on my own
 

Simon Knight  

I am so excited to try it.
 

Liz Haskin  

It's helped me greatly - I was determined to do this, and this is the only place I've found with success to assist. My company uses a bespoke Access app for pretty much everything, including creating invoices. We previously used Sage (Line 50) which wasn't easy to deal with, so everything was entered a second time, manually.

Now, when an invoice is created in the app all of the details are fired off to Xero. If payment comes into Xero, this is fed back to our Access app. Also if an invoice is voided on either side that mirrors.

If an e-mail comes in to our accounts address, our Access app checks the sender and the format. If it's an invoice, it gets sent straight to Xero as a draft for approval.

We've also had to deal with a separate external billing system in recent years, and because that also has its own API I've been able to integrate that with our Access app and Xero too. Everything now works seamlessly.

I write Access apps (usually backended on to SQL Server) for others too, and will definitely use this integration. Thanks to the OP for cracking this nut.
 

John Crighton  

Amazing. I looked at the original version today and now that I know more about vba I could probably make it cleaner. Did anyone crack the encryption without using OpenSSL?
 

Tapaha Robati  

Simon & John, your experience with this is just the input I needed.
I am going to start working on my access to xero connection in week or so's time. But I am starting to formulate a plan now.
👍
 

Liz Haskin  

Unfortunately not, in all honesty unless I have a case where I can't use OpenSSL then I'd continue to use it as it is because it works perfectly.

I did have a crack at it though, but could never get the same result as OpenSSL gave me. I didn't spend a great deal of time on it, and I feel like I must have been missing something obvious.
 

John Crighton  

Good luck Liz, let us know if you get stuck anywhere!
 

John Crighton  

Sounds like a challenge bro haha. The only way I originally cracked this in the first place was following what the c# version did like by line and emulated it. I guess I could try do this but it’s probably built in to a library. #hackathon
 

Tapaha Robati  

Hi Tapaha, Your code is still working for us, so thank you again for sharing it. The mod I made does handle clock changes.

We used the Xero contact number/code field to store our own unique ID, and this caused us an issue because a user merged two contacts and archived the one with the contact code. The API cannot simply add the contact code on to the active contact because it must be unique including archived contacts.

I agree OpenSSL is not ideal, but I expect another solution will take a lot of development time, so not feasible here. From what I remember, renewing the certificate after 2 years will probably be painful, as I will have forgotten how it all works by then!
 

David Nye  

Has anyone looked into the update to OAuth 2.0?
 

David Nye  

As far as I can see, the update to OAuth 2 is going to make Tapaha's code unusable from next March. Instead we will need to use the XOAuth tool provided by Xero. I have got this working from Access VBA, and can retrieve the Access, Refresh and Identity tokens, but I cannot yet see how the integration code needs to be modified to provide the authentication to the API calls. The only Xero documentation on this seems to be at https://developer.xero.com/documentation/oauth2/auth-flow . So I think I have completed step 4 and I am now working on step 5. Any help will be very gratefully received!

Many thanks,

David
 

David Nye  

David...good point. TBH, I let this project go hoping someone could pick it up and run with it. Seems like you did well with it.
What I've moved on to is Python...because in my new job it's just something I wanted to learn. I've since come to the realisation that because the package for Python is I guess you could say "more user friendly", I'm working on ways to integrate python execution via VBA. Part of my projects is making a Xero interface that runs off Python because of a web app I'm producing. In turn...I'll transform it in to a package that's freely distributed for anyone using VBA.
I'm a while away, but when it's done...I'll get it here for everyone to use. Click a button in Access...and it executes what you need via python.
 

Tapaha Robati  

I'm working on oAuth2 for VBA but also looking at the option of using .net to update SQL Server tables then dealing with those in VBA using the code I already have in production. The problem with .net and VBA is that the C# SDK is not com enabled. I tried to create a .net addon (dll) that could be called from VBA and mde it com enabled but since it called the SDK that wasn't, it didn't work.
 

Kent Gorrell  

I now have the API calls working, as well as the token refresh so that the user only needs to authorise the OAuth 2.0 API access once. Currently XOAuth seems buggy on Windows; I have reported these to api@xero.com so hopefully will be fixed soon. The end result will be very much simpler VBA code than we were using for OAuth 1.0, since the complexity is all in XOAuth. I wrote this from scratch rather than trying to modify Tapaha's OAuth 1.0 module. Contact me via www.dnye.co.uk if you wish to see it.
P.S. This new version eliminates OpenSSL and certificate handling.
 

David Nye  

I have to admit I wasn't aware this was happening. I have successfully used OAuth 2.0 in a separate project however, and handled everything in VBA that time with no outside libraries. Hopefully I can use some of that. I look forward to trying soon.
 

John Crighton  

Hi John, It's the callback requirement which makes the Xero implementation difficult; they are not supporting the Client Credentials flow. Please let us know if you find a way to handle that in VBA without using xoauth, thanks.
 

David Nye  

My last 2 posts on this page disapeared!

Now happily retrieving data and parsing the XML to local tables using oAuth2 and nothing but VBA. with some help from David Nye and
Tim Hall - https://github.com/VBA-tools/VBA-Web

I'm using xoAuth to get the Tokens in the first instance, then using Davids function to refresh them before each call. Able to download > 10k records in minutes on the first call, after that I'll use the
.SetRequestHeader "if-modified-since", utcEditedSince
in the header to just get changed records
 

Kent Gorrell  

Kent, that's great news. I'd used 'VBA-Web' for a previous project using OAuth 2, but that didn't require callback, as David rightly points out achieving this entirely through VBA would be impossible(?) without some kind of middleware (in this case xOAuth) as there is no way to accept the callback.
 

John Crighton  

Hi John, Your post yesterday has also disappeared! However, this is my reply to that now missing post...
Thanks John, I have now worked around the xoauth bugs I encountered with scripting (see the new thread I started), so in my app an end user can now run "xoauth connect" to authorise Xero access from a button click. I think this resolves the issue with the Refresh inevitably failing at some point, and also simplifies setup a bit. The remaining issue is that xoauth needs to be installed and its config file created (e.g. using the buggy "xoauth setup") on each computer/user that might want to authorise access. I think that the config file creation can be automated. I guess xoauth installation can be scripted, but that would perhaps potentially be dangerous.
 

David Nye  

Thanks for the credits, Kent and John. It would be good to eliminate xoauth by using some component already installed on Windows 10? Next best for me would be a single library file installed simply by copying to the front end folder. Less convenient would be a self contained script we could put on any web server.
 

David Nye  

Hi
I'd be very interested to know if you've got to a point where you reckon I can swap out the code yet, if so where do i get it from and if not when and if there's anything I can do to help....
I use Tapaha's "Version 1" code every month to upload my PDF invoices into Xero etc from my on-premise Access solution and its kinda mission critical
 

Simon Knight  

xoAuth does need to be run on each machine that will be used to commuinicate with the API because it stores data in a config file.
I couldn’t see, in the Token Refresh, how any xoAuth data gets sent but apparently it does.

The downside is that my client must setup xoAuth on each machine that needs to do the download but that’s only once or two.
And my client needs to give me appropriate permissions so I can setup my development environment to allow me to test the movement of data from the tables that are populated by the API to their application’s tables.

Other forms of authorisation require a response from a browser when the user logs into Xero.
In .Net, the authorisation methods I’ve seen, create an instance of a form/browser that it (the .Net app) controls for the user to log in then it waits for a response.
VBA can’t do that.

In the long term a .Net app to get the initial Tokens may be viable but a COM enabled .Net app may be difficult or impossible
So the VBA would need to shell out to it and once the user completes their authorisation, the .Net app would write the tokens locally (SQL Server or ACCDE?)
And then the user would initiate the continuation of the download in their application. Yuk.

For the time being xoAuth will do the job.
And it does have the advantage that you don’t need to do the Xero login and authorisation every time you want to do a download. But some may see this as an issue.

I’ve built tables for the XML to populate for most of the Xero endpoints that I'll be using
And tested the XML for those.
It’s a bit clumsy with all the inconsistencies and child nodes in Xero’s XML but it work OK for now.

I haven't tried putting data up yet. That's not a priority for me. But that shouldn't be too difficult.

Give me 3 or 4 days to finish and cleanup and I'll be ready to share.


 

Kent Gorrell  

The converted version of my app is now running live i.e. using OAuth2 instead of OAuth1, and uploading invoices one by one to Xero in the background, when they are issued. This is still "refreshing" from the initial token set generated on my own pc a couple of weeks ago. So I have not yet needed to install xoauth on site, and of course there is no absolute need to do so; I can initiate a new token set here if necessary, since my own Xero login has the necessary permissions. However, I prefer my clients to be self sufficient, so in the next update I will include the function to run "xoauth connect", which opens a browser to ask the user to approve Xero access, and my (very simple) code then collects the output token set and stores it in the database ready for the next invoice posting. I no longer think it worthwhile trying to automate "xoauth setup"; instead I have simply added this step to the end of my instructions on how to install xoauth.

I'm very willing to share my code, but do not plan to produce a self contained app to demonstrate it, so it's great news that Kent intends to do this.
 

David Nye  

The token refresh sends the "client id" and "Client secret" (both obtained from the Xero App setup screen) in the request headers and the last "Refresh token" (from either "xoauth connect" or the last refresh) in the request body. The refresh request returns an access token (valid for 30 minutes) to authenticate API requests, and a new refresh token (valid for 60 days). API requests also need to include a "Tenant ID" (typically a Xero organisation ID) which was included in the original Xero access authorisation (the Xero app may be authorised to access more than one Xero organisation e.g. both Demo and live companies or multiple live companies).

Does that help?
 

David Nye  

Thanks to David for a better explanation of how the xoauth process works. I was wrong, there is no config file.

Apparently xoAuth was create in response to feedback from developers who had autonomous applications
Ie neither need or wanted a user to log in every time they called the API

I’m now storing the refresh token in the BE so any instance of my application can access it, use it and refresh it.
Note to self: make sure two machines can’t run the process at the same time…
 

Kent Gorrell  

Using our new process the only time we need to run xoauth is
if someone loses the refresh token like I did yesterday
when I overwrote my test data with data from production
since xoauth is setup this only requires
xoauth connect MyAppName
and the copying and pasting of the result.

I’m now keeping a copy or the refresh token in the FE as well as the BE.
The tables are in the BE and I’ve used ‘tbx’ as the prefix
to avoid clashing with table names in any application this may work with
 

Kent Gorrell  

When you run "xoauth setup" it creates/updates a config file, such as "Users/David/.xoauth/xoauth.json", which is then read each time you run "xoauth connect". However, this is plain text, so the client secret is not stored in this config file, instead it is stored in Windows Credential Manager. Therefore, the config file cannot simply be copied, and "xoauth setup" must be run on each user profile which will need to run "xoauth connect".
 

David Nye  

David, thanks for that further clarification.

The XML parsing is now recursive, way less code as it drills down into nodes
In some cases these lower level nodes have the same name as other nodes
so the fields they write to are named ParentNode_Node

just a bit of final testing and documenting and I’ll be ready to share.
 

Kent Gorrell  

David, Kent. I hope you're both up and working with xOAuth and OAuth 2.0. I must admit that I'm a little out of my depth on this one, and I'm going to struggle to achieve what you have in the time.

Do either of you have anything that you'd be willing to share at the moment?
 

John Crighton  

Hi John, Yes, my interface is running fine thanks. Kent has done a lot more work on it than I have; I think even his unfinished sample db will probably be more use to you than what I originally sent to Kent. So let's see what he says. Otherwise I'll be glad to help.
 

David Nye  

Thanks David, I'll wait as you say. I don't like Xero's approach to this having read the other threads you've contributed to. It does seem like a bit of a cumbersome solution on their part.
 

John Crighton  

The decision not to support the Client Credentials flow certainly seems counter productive; it will lose Xero many customers for no good reason. Their argument that automated processes should be authorised in the same way as normal input sounds reasonable, until you look in detail at what we are having to do to implement the alternative.

On the other hand, our shared OpenSSL solution for OAuth 1 was, in my view, even more cumbersome. At least the "messy" part can now be done up front by IT staff on their own pc, and the runtime code no longer needs to create text files and shell out to batch files and run OpenSSL on each user pc. It just needs the tokens and one extra API call to refresh them.
 

David Nye  

That is true, although I expect it might be possible without OpenSSL. It certainly seems there is no way to make the new implementation more straightforward, however I'm not entirely sure what the solution includes yet!
 

John Crighton  

All, I've been rather busy with implementation, apparently the old methodology did not take into account all the Xero invoices with a status of Deleted or Voided. They are the result of the semi automated process of exporting to csv then importing into Xero. Users frequently imported AccPay invoices as AccRec and visa v.

The good news is that xoAuth is working fine. We have only needed to renew the Tokens once. But this beats having to have a user manually log in every time the application wants to connect to Xero's API. I've installed xoAuth on my client's PC and setup the app so he only needs to run xoauth connect to get fresh credentials that he copies back to his application. The tokens live in the BE db so they can be shared by users. Xero only allows two private applications to connect to the same organisation so our test environment needs to share tokens with production while I use my own for (offsite) development.

I've only been getting invoices (and Contacts) from Xero so far, I've not started on the posting to the API. Still doing that via CSV export/import.

Because I've been so focussed on implmentaion, I haven't had a chance to update the original demo/test app to get it to a presentable state.
but here is what it does offer -
• xoAuth token refresh
• get from API for most of the Endpoints that Tapaha had in his 1.0 version
• xml parsing of responses into local tables

To be continued...
 

Kent Gorrell  

I also produced some documentation which should help you get your head around it. Contact me by email via dnye.co.uk and I'll send it over.
 

David Nye  

David, thank you. I've sent you an e-mail.
 

John Crighton  

Well, thank you David for your help. I've managed to get on top of xOAuth and understand a bit more about how everything works. I now have a working OAuth 2.0 setup, and have a function which takes in similar variables to Tapaha's original function and produces the same output. This saves a big rewrite!

I have included a timestamp for the refresh token in my BE, so that a new one is only obtained if the current one is >25 mins old. This makes things a bit quicker, and I was also a bit worried about multi-user clash over refresh tokens (whether that is a well founded worry or not I'm unsure).

Thanks David and Kent.
 

John Crighton  

Any one keen on doing some contacted work for us. We are migrating from MYOB to XERO. I have written code in VBA to move invoices and stock in and out of MYOB using ODBC and connector. Now I need to rewrite it so the Access Database can send and receive info to XERO. Anyone keen or know anyone?
 

Michael Forte  

Hi Michael, Hopefully Kent will help you, since he is in your part of the world. I am in the UK but happy to help if I can. See http://dnye.co.uk/
 

David Nye  

@ Michael, contact me - kent at osel.com.au, we can at least have a chat.
 

Kent Gorrell  

I had previously tried to post some xOAuth instructions here, but my post never got to the thread. The instructions originate from David, and I've adapted them slightly to make them a bit more foolproof. They helped me greatly. I'll try to break up the content and post below as there may be a size limit perhaps..(?)
 

John Crighton  

Create a Xero App at https://developer.xero.com/myapps/
1. App name (name of your app, anything you like)
2. Select “Auth Code Web App”
3. Company URL (your website – not critical but required)
4. OAuth 2.0 redirect URI: http://localhost:8080/callback (not critical, but required)
5. Copy the client ID and secret to paste into your code or backend. Don’t lose the secret.

Install XOAuth on Windows – this only needs to be done on one machine in the organisation:
6. Open PowerShell

7. Install Scoop – to do this type the following commands:
(a). Invoke-Expression (New-Object System.Net.WebClient).DownloadString('https://get.scoop.sh')
(b). scoop install git

8. Install XOAuth
(a). scoop bucket add xeroapi https://github.com/XeroAPI/scoop-bucket.git
(b). scoop install xoauth
 

John Crighton  

9. Setup XOAuth
Run “xoauth setup <name of your package>” (make up your own package name)
(a). ? What's the Authority? https://identity.xero.com (just hit enter)
(b). ? What's your client_id? (take from step 5)
(c). ? Select Grant Type: authorization_code
(d). ? What's your client_secret? (take from step 5)
(e). ? Add scope (`d` when done) openid (This may already be included)
(f). ? Add scope (`d` when done) offline_access (This may already be included)
(g). ? Add scope (`d` when done) accounting.contacts (permits read/write to contact records on Xero)
(h). ? Add scope (`d` when done) accounting.transactions (permits read/write to transaction records on Xero)
(i). ? Add scope (`d` when done) accounting.settings.read (permits read only access to account settings)
See list of other scopes here and add any if applicable: https://developer.xero.com/documentation/oauth2/scopes
(j). ? Add scope (`d` when done) d

10. Authenticate and get tokens
(a). Run “xoauth connect <name of your package>”
(b). Log in to Xero and authorise app access to your app. – This may fail first time with ‘Token not ready yet’ but should work second time.
(c). Copy the access and refresh tokens and add them to your backend so that any client that needs to make an API call can see them.
 

John Crighton  

Note - use an app name with no spaces. xoAuth seems to ignore the part of the name after the space. and you can't use 'xero' in the app name.

you can only have two private apps that connect to an organisation. So that means only two sets of credentials. In my case I've used one for off site development and the other set of tokens for production which is shared with the test environment.
 

Kent Gorrell  

Hi Guys

I've left this far later than I should have but has anyone got the sample code and instructions as to how to use it please? I now really have to get my head out of the sand and make my Access database work in the new world

Any help much appreciated

Simon
 

Simon Knight  

Hi Simon, I have sent you my solution by email. You may have longer than you think, and another option; did you see this? https://developer.xero.com/announcements/custom-integrations-are-coming/
 

David Nye  

P.S. Spoiler: They have now decided to support the Client Credentials flow, but to charge extra for this. Lots of customers are very angry! This also means they will support v1 apps for longer while they get it ready. No date yet announced.
 

David Nye  

BTW, Is anyone receiving "Xero Developer Newsletters"? I've subscribed several times over the years, but have never received anything fitting that description!
 

David Nye  

Thanks hugely for all of that David. I will look at over the next few days and let you know. I await further developments from Xero with interest and thanks for the heads up. With regards to money I get that it annoys people - from my seat I'spose it depends how much it is...

No I've never received any newsletters despite subscribing myself
 

Simon Knight  

David
Thanks very much for all your help over the past few days... I've made it work!!!!

Well that is to say I've connected via oAuth2 and your final test reports "SUCCESS" and returns my last updated invoice number

Wooo hooo
Cheers muchly
Simon
 

Simon Knight  

I've come late to this party and find myself urgently needing a VBA OAuth2 solution too.

Does anyone have a working example of VBA code?

David, I hope you don't mind if I contact you for a code sample. Although I see Xero have custom integration solutions in the works, which may help my pain.

thanks,

Hugh
 

Hugh B  

Hi Hugh, I sent you the package by email.
 

David Nye  

Hi David, could I also please get an email copy to fcakdg@gmail.com?
Would love to get a working solution too!
 

Dean Graham  

Hi Dean, it's on its way. Good luck.
 

David Nye  

Hello community. David, do you mind sending me the sample as well please? Thank you so much. ally at homeofficeheaven.co.za
 

Allyson Ben-Israel  

No problem Ally, email sent. Hey Hugh and Dean, please post to let us know how you're getting on, thanks.
 

David Nye  

HI David, would it be possible to get a copy sent to president@tcfqld.org.au I would love to automatically synch contacts and invoices with information from an access membership DB for our non-profit. THanks
 

Peter Hobson  

Hi Peter, It's on its way. I hope it helps.
 

David Nye  

Hi David, could I please get a copy sent to steve@dbt.co.nz, moving from ODBC in Infusion to XERO...
 

Stephen Baggaley  

I see that a new version of xoauth was released in September 2020, and this appears to fix the issues I reported. Unfortunately the memory is too hazy for me to guess if this might make it possible to automate fully from VBA. Even if it does, we will still have the over complex installation procedure. Ideally we need someone really clever to replace xoauth with pure VBA, or use a built in Windows component for the call back, or provide some other solution which is very easy to distribute and run.
https://github.com/XeroAPI/xoauth/releases
 

David Nye