(Excel) Xero API through an SSH Tunnel

This example demonstrates connecting through an SSH Tunnel w/ 2-legged OAuth for a Xero private application.

This example requires Chilkat v9.5.0.64 or later

Download Excel Class Modules Chilkat Excel Class Modules

' This example requires Chilkat v9.5.0.64 or later

Dim tunnel As Chilkat.Socket
Set tunnel = Chilkat.NewSocket


sshHostname = "www.my-ssh-server.com"

sshPort = 22

' Connect to an SSH server and establish the SSH tunnel:

success = tunnel.SshOpenTunnel(sshHostname,sshPort)
If (success <> True) Then
    Debug.Print tunnel.LastErrorText
    Exit Sub
End If

' Authenticate with the SSH server via a login/password
' or with a public key.
' This example demonstrates SSH password authentication.
success = tunnel.SshAuthenticatePw("mySshLogin","mySshPassword")
If (success <> True) Then
    Debug.Print tunnel.LastErrorText
    Exit Sub
End If

'  OK, the SSH tunnel is setup.  Now open a channel within the tunnel.
'  (Any number of channels may be created from the same SSH tunnel.
'  Multiple channels may coexist at the same time.)

' This sample code would typically be placed in a subroutine or function
' where the rest object is passed by reference.
' It does the OAuth1 setup and makes the initial connection.
Dim rest As Chilkat.Rest
Set rest = Chilkat.NewRest


bTls = True

port = 443

maxWaitMs = 7000

' This returns a socket object that is a single channel within the SSH tunnel.
Set channel = tunnel.SshOpenChannel("api.xero.com",port,bTls,maxWaitMs)
If (tunnel.LastMethodSuccess <> True) Then
    Debug.Print tunnel.LastErrorText
    Exit Sub
End If

' Use the connection.  (This connection is a TLS running on an SSH channel through an SSH tunnel.
' In other words, TLS is wrapped within the SSH tunnel.)
success = rest.UseConnection(channel,True)
If (success <> True) Then
    Debug.Print rest.LastErrorText

    Exit Sub
End If

' OK, we're connected.  
' -----------------------------------------------------------------
' Now setup the OAuth1 authenticator..


consumerKey = "XERO_PRIVATE_APP_KEY"

consumerSecret = "XERO_PRIVATE_APP_SECRET"

' Let's get our private key from our PFX (password protected), or the PEM (unprotected).
' You can decide which to use.  Either is OK, although I would recommend keeping your
' private keys in a PFX and not in an unprotected PEM.
Dim pfx As Chilkat.Pfx
Set pfx = Chilkat.NewPfx
success = pfx.LoadPfxFile("qa_data/certs/xero_private_app/public_privatekey.pfx","PFX_PASSWORD")
If (success <> True) Then
    Debug.Print pfx.LastErrorText
    Exit Sub
End If


Set privKeyFromPfx = pfx.GetPrivateKey(0)
If (pfx.LastMethodSuccess <> True) Then
    Debug.Print pfx.LastErrorText
    Exit Sub
End If

' Or we can load from a PEM..
Dim privKeyFromPem As Chilkat.PrivateKey
Set privKeyFromPem = Chilkat.NewPrivateKey
success = privKeyFromPem.LoadPemFile("qa_data/certs/xero_private_app/privatekey.pem")
If (success <> True) Then
    Debug.Print privKeyFromPem.LastErrorText
    Exit Sub
End If

' Note: There are many other means for loading a private key, including
' from other formats and directly from memory (i.e. not file-based).

Dim oauth1 As Chilkat.OAuth1
Set oauth1 = Chilkat.NewOAuth1
oauth1.ConsumerKey = consumerKey
oauth1.ConsumerSecret = consumerSecret
oauth1.Token = consumerKey
oauth1.TokenSecret = consumerSecret
oauth1.SignatureMethod = "RSA-SHA1"
success = oauth1.SetRsaKey(privKeyFromPfx)

' Install the OAuth1 authenticator.
success = rest.SetAuthOAuth1(oauth1,False)

Debug.Print "OK, the Xero OAuth1 is initialized and the REST object is ready to make REST API calls.."

' -----------------------------------------------------------------
' Make a call to verify that OAuth1 through an HTTP proxy works..

' Get the full list of contacts.
Dim sbXml As Chilkat.StringBuilder
Set sbXml = Chilkat.NewStringBuilder
success = rest.FullRequestNoBodySb("GET","/api.xro/2.0/Contacts",sbXml)
If (success <> True) Then
    Debug.Print rest.LastErrorText
    Exit Sub
End If

' A 200 response is expected for actual success.
If (rest.ResponseStatusCode <> 200) Then
    Debug.Print sbXml.GetAsString()
    Exit Sub
End If

' Iterate over the contacts..

bAutoTrim = False
Dim xml As Chilkat.Xml
Set xml = Chilkat.NewXml
success = xml.LoadSb(sbXml,bAutoTrim)
success = xml.SaveXml("qa_cache/xero_contacts.xml")

' How many records exist?

recordCount = xml.NumChildrenAt("Contacts")
Debug.Print "numRecords = "; recordCount


i = 0
Do While i < recordCount
    xml.I = i
    Debug.Print "ContactID: "; xml.GetChildContent("Contacts|Contact[i]|ContactID")
    i = i + 1
Loop