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:

VBA Json

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 Sub

Let’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 = Nothing

The 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 = Nothing

The 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.

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.