Technical Articles
Integrating Concur APIs with Excel using VBA
Introduction
Hello Community! I’m Gilliatti Paparelli and I’ve been at SAP Concur for 5 years as Expense and Request Consultant and joined the Technical Consultant role a year ago.
As Technical Consultants we are challenged with different implementation requirements that require unique or innovative approaches.
In this blog post I’ll show you how you can use the Concur APIs to integrate with Excel using VBA.
Context
Every now and then we are required to perform repetitive tasks during our implementations. As a result, my objective was to build a tool that can be re-used for many different purposes and can also be shared and understood by non technical users.
Before we begin
Make sure you have access to Concur webservices service, this is required for the following instructions to work. Also familiarize yourself with the authentication process and how to create apps and grant permissions in Concur.
A good place to start is the link below:
https://developer.concur.com/api-reference/authentication/getting-started.html
This post assumes you are already familiar with how the APIs work and are able to interact with them using some market tool such as Postman.
You also need to have minimum knowledge on Excel VBA as this is not meant to be a beginner tutorial. There are a lot of good training material specific for Excel if you need a refresher on the product.
With the expectations set up, let’s begin with the tutorial.
Setting up the environment
When it comes to excel you’ll need to perform some initial tasks prior to utilizing VBA.
First of all you need to add the developer tab if you have not done it yet. For this, follow the steps below:
In Excel select File and Options:
Adding the developer tab to Excel
While you are there go to the Trust Center and enable Macros if you have not done so before:
Trust Center > Trust Center Settings > Enable all macros
From there you should find the developer tab among the other tabs.
By selecting the Visual Basic button you are able to access the VBA module.
Inside the VBA we will import one additional library to work with Json files.
The Json module we’ll be using can be downloaded from this repository:
Download and extract the latest version of it.
On VBA, import the library following the GIF below:
Adding the Json Library
Select Tools -> References and add the following references to your project:
- Microsoft XML ( v6.0 for the latest version of Ms Office )
- Microsoft Scripting Runtime
With all done you should now be able to generate and parse Json files and call REST APIs with the MSXML2 object.
Generating the acess token
Assuming you have already created your app in Concur with the correct grants and permissions and already have your refresh token ( see here ), we’ll create the access token for the session.
The access token is used to authenticate for the other API calls and lasts for approximately one hour. Once it expires you will need to request a new one.
You can retrieve a new one with the following API: /oauth2/v0/token combined with your gateway, in my case the complete URL will be:
https://us2.api.concursolutions.com/oauth2/v0/token
An example in postman how to retrieve this token:
Now let’s perform the same call on VBA:
In my example, I have created input cells for the various parameters and a button to make the call.
And here is the sample code:
Private Sub BtnBearer_Click()
Endpoint = Cells(1, 2) & "/oauth2/v0/token"
Payload = "client_id=" & Cells(2, 2) & "&client_secret=" & Cells(3, 2) & "&grant_type=refresh_token" & "&refresh_token=" & Cells(4, 2)
Dim Json As Object
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
xmlhttp.Open "POST", endpoint, False
xmlhttp.setRequestHeader "User-Agent", "HTTP/1.1"
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xmlhttp.setRequestHeader "Accept", "application/json"
xmlhttp.setRequestHeader "Host", "us2.api.concursolutions.com"
xmlhttp.setRequestHeader "Connection", "close"
xmlhttp.setRequestHeader "Content-Length", "167"
xmlhttp.setRequestHeader "concur-correlationid", "ExcelVBA-Gpaparelli"
'On Error Resume Next
xmlhttp.Send Payload
Debug.Print xmlhttp.ResponseText
Set Json = ParseJson(xmlhttp.ResponseText)
Cells(7, 2).Value = Blank
Cells(7, 2).Value = Json("access_token")
Set xmlhttp = Nothing
End SubLet’s explain what is happening:
endpoint = Cells(1, 2) & "/oauth2/v0/token"
Payload = "client_id=" & Cells(2, 2) & "&client_secret=" & Cells(3, 2) & "&grant_type=refresh_token" & "&refresh_token=" & Cells(4, 2)- “Endpoint” will receive the concatenation of the gateway + the API
- “Payload” will receive all the body parameters of the API call, something like this:
"username=$username&password=$password&grant_type=password&client_secret=$c
lient_secret&client_id=$client_id"
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
xmlhttp.Open "POST", endpoint, False
xmlhttp.setRequestHeader "User-Agent", "HTTP/1.1"
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xmlhttp.setRequestHeader "Accept", "application/json"
xmlhttp.setRequestHeader "Host", "us2.api.concursolutions.com"
xmlhttp.setRequestHeader "Connection", "close"
xmlhttp.setRequestHeader "Content-Length", "167"
xmlhttp.setRequestHeader "concur-correlationid", "ExcelVBA-Gpaparelli"
'On Error Resume Next
xmlhttp.Send Payload- We create an object reference to MSXML2.XMLHTTP to handle HTTPS calls
- Using the “.open” method we will setup the call to the corresponding URL and action “POST”
- Using the “.Send” method will perform the call passing the payload in the body
Set Json = ParseJson(xmlhttp.ResponseText)
Cells(7, 2).Value = Blank
Cells(7, 2).Value = Json("access_token")- Concur response will return in the xmlhttp.ResponseText variable
- Then we use the ParseJson function to parse the response into the Json dictonary
- The cell with bearer token will be updated with the tag “access_token” from the parsed json response
And we get this result:
The code retrieves the bearer (access) token
From here you can use the same logic to call any other API using the just retrieved token.
Use-case example
Now let’s see one real use case for this integration. For this we will create a program that can create and issue cash advances for employees automatically.
As you can see, I created a few buttons to perform each API call. The first will get user IDs based on login IDs since this is required for the following API calls.
The second button will create the cash advances for users and the third one will issue the cash advances.
The result:
Now let’s see each API call individually:
First the Get user ID API:
Dim Json As Object
Dim Line As Integer
Dim iLines As Integer
Dim iCount As Integer
Endpoint = Worksheets("Credentials").Cells(1, 2) & "/profile/identity/v4/Users?filter=userName eq "
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
iLines = Worksheets("Cash Advances").UsedRange.Rows.Count
iCount = 4
If iCount > iLines Then
Stop
End If
While iCount <= iLines
If (Cells(iCount, 1).Value <> "X") And (IsEmpty(Cells(iCount, 3))) Then
Current_Endpoint = Endpoint & Cells(iCount, 2)
xmlhttp.Open "GET", Current_Endpoint, False
xmlhttp.setRequestHeader "Content-Type", "application/json"
xmlhttp.setRequestHeader "Accept", "application/json"
xmlhttp.setRequestHeader "concur-correlationid", "ExcelVBA-Gpaparelli"
xmlhttp.setRequestHeader "Authorization", "Bearer " & Worksheets("Credentials").Cells(7, 2)
xmlhttp.Send
Debug.Print xmlhttp.ResponseText
Set Json = ParseJson(xmlhttp.ResponseText)
For Each Item In Json("Resources")
Debug.Print Item("id")
Cells(iCount, 3).Value = Item("id")
Next Item
Set Json = Nothing
Set Item = Nothing
End If
iCount = iCount + 1
Wend
'Release object
Set xmlhttp = NothingThe code explained:
Endpoint = Worksheets("Credentials").Cells(1, 2) & "/profile/identity/v4/Users?filter=userName eq "
- Setting up the base endpoint URL of the API call.
iLines = Worksheets("Cash Advances").UsedRange.Rows.Count
iCount = 4
If iCount > iLines Then
Stop
End If
While iCount <= iLines
If (Cells(iCount, 1).Value <> "X") And (IsEmpty(Cells(iCount, 3))) Then
Current_Endpoint = Endpoint & Cells(iCount, 2)- The iLines will receive the number of rows with data
- The iCount will start with 4 witch is the first non-header row
- Then we loop throgh lines and build the endpoint with the employee login ID and make the API call
Set Json = ParseJson(xmlhttp.ResponseText)
For Each Item In Json("Resources")
Debug.Print Item("id")
Cells(iCount, 3).Value = Item("id")
Next Item
Set Json = Nothing
Set Item = Nothing
End If
iCount = iCount + 1
Wend- We will parse the response to a dictionary. In this case we need to look further in the ‘Resources’ property and find the ‘ID’ property witch contains the user ID.
- We move the user ID to the corresponding cell value and then clear the objects.
Creating the cash advance:
Dim items As New Collection, myitem As New Dictionary, amounts As New Dictionary, i As Integer
Dim Payload As String
Dim Line As Integer
Dim iLines As Integer
Dim iCount As Integer
iLines = Worksheets("Cash Advances").UsedRange.Rows.Count
iCount = 4
If iCount > iLines Then
Stop
End If
While iCount <= iLines
If (Cells(iCount, 1).Value <> "X") And (IsEmpty(Cells(iCount, 7))) Then
Set items = Nothing
Set myitem = Nothing
Set amounts = Nothing
amounts("currency") = Cells(iCount, 6).Value
amounts("amount") = Cells(iCount, 5).Value
myitem.Add ("amountRequested"), amounts
'myitem("comment") = "Comment Text"
myitem("name") = Cells(iCount, 4).Value
'myitem("purpose") = "Purpose text"
myitem("userId") = Cells(iCount, 3).Value
items.Add myitem
Payload = ConvertToJson(myitem, Whitespace:=2)
Debug.Print Payload
'Call Concur to create cash advance
Endpoint = Worksheets("Credentials").Cells(1, 2) & "/cashadvance/v4.1/cashadvances"
Cells(iCount, 7).Value = Blank
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
xmlhttp.Open "POST", Endpoint, False
xmlhttp.setRequestHeader "Content-Type", "application/json"
xmlhttp.setRequestHeader "Accept", "application/json"
xmlhttp.setRequestHeader "concur-correlationid", "ExcelVBA-Gpaparelli"
xmlhttp.setRequestHeader "Authorization", "Bearer " & Worksheets("Credentials").Cells(7, 2)
xmlhttp.setRequestHeader "Content-Length", "100"
xmlhttp.Send Payload
Debug.Print xmlhttp.ResponseText
Set Json = ParseJson(xmlhttp.ResponseText)
Debug.Print Json("cashAdvanceId")
Cells(iCount, 7).Value = Json("cashAdvanceId")
Set Json = Nothing
Set xmlhttp = Nothing
End If
iCount = iCount + 1
Wend
The code explained:
Dim items As New Collection, myitem As New Dictionary, amounts As New Dictionary, i As Integer
Dim Payload As String
.....
Set items = Nothing
Set myitem = Nothing
Set amounts = Nothing
amounts("currency") = Cells(iCount, 6).Value
amounts("amount") = Cells(iCount, 5).Value
myitem.Add ("amountRequested"), amounts
'myitem("comment") = "Comment Text"
myitem("name") = Cells(iCount, 4).Value
'myitem("purpose") = "Purpose text"
myitem("userId") = Cells(iCount, 3).Value
items.Add myitem
Payload = ConvertToJson(myitem, Whitespace:=2)
This api differs from the others because it will request you to send a json on your request.
The following is an example request:
{
"amountRequested": {
"currency": "USD",
"amount": "10"
},
"comment": "This cash advance was issued by API",
"name": "Cash advance API 1",
"purpose": "Cash advance via API",
"userId": "dc6cd529-bf69-4a93-ace9-XXXXXXXXXX"
}This can be achieved with the “ConvertToJson” function.
First I created a dictionary array with all the expected parent and child nodes and then passed it to the function that will return the formatted json as a string variable.
From there I can call the API passing the Json on the payload and retrieving the CashAdvanceID from the response. The variable is then moved to the corresponding cell.
Issuing the cash advance:
The same logic can be implemented to call the cash advance issue API. Here is the sample code:
Dim items As New Collection, myitem As New Dictionary, i As Integer
Dim Payload As String
Dim Line As Integer
Dim iLines As Integer
Dim iCount As Integer
Endpoint = Worksheets("Credentials").Cells(1, 2) & "/cashadvance/v4.1/cashadvances/"
iLines = Worksheets("Cash Advances").UsedRange.Rows.Count
iCount = 4
If iCount > iLines Then
Stop
End If
While iCount <= iLines
If (Cells(iCount, 1).Value <> "X") And (IsEmpty(Cells(iCount, 9))) Then
Set myitem = Nothing
'myitem("comment") = "Comment Text"
myitem("exchangeRate") = Cells(iCount, 8).Value
'items.Add myitem
Payload = ConvertToJson(myitem, Whitespace:=2)
Debug.Print Payload
'Call Concur to issue cash advance
Current_Endpoint = Endpoint & Cells(iCount, 7).Value & "/issue"
Cells(iCount, 9).Value = Blank
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
xmlhttp.Open "POST", Current_Endpoint, False
xmlhttp.setRequestHeader "Content-Type", "application/json"
xmlhttp.setRequestHeader "Accept", "application/json"
xmlhttp.setRequestHeader "concur-correlationid", "ExcelVBA-Gpaparelli"
xmlhttp.setRequestHeader "Authorization", "Bearer " & Worksheets("Credentials").Cells(7, 2)
xmlhttp.setRequestHeader "Content-Length", "100"
xmlhttp.Send Payload
Debug.Print xmlhttp.ResponseText
Set Json = ParseJson(xmlhttp.ResponseText)
Debug.Print Json("status").Item("name")
Cells(iCount, 9).Value = Json("status").Item("name")
Set Json = Nothing
Set xmlhttp = Nothing
End If
iCount = iCount + 1
Wend
'Release object
Set xmlhttp = NothingThe json in the body of this call is something like this:
{
"comment": "Issued via API",
"exchangeRate": 1.00000
}
Side notes
Upon my experimenting with using the VBA integration, I would sometimes get an error when trying to perform the call to Concur, something like this: “the download of the specified resource has failed”
In my experience, most of the times just retrying would result in a successfull call. In some other cases I noticed that a “GET” method would always “go through”, and other methods would work after the first successful call.
If none of the above work you can try using the “www-” version of the base URL, i.e. “https://www-us2.api.concursolutions.com” instead of “https://us2.api.concursolutions.com”
Another thing worth mentioning is that Excel is sensitive when it comes to numbers in cells and Json expects a very specific number format. An easy solution was to format all data as text to avoid conversions. If you’re looking for a more elegant solution you can use VBA functions to perform the conversions.
Closing thoughts
With this post I shared my experience and findings with using VBA to automate tasks in Concur.
Hopefully it can help others achieve positive results with Excel and Concur.
Thank you for reading until the end and see you next time.