REST API - Examples using Excel - Part 2

Introduction

This post leads on from the foundation created in Part 1 and will require the underlying modules, procedures and connections to work correctly. If you have not gone through Part 1, please do so before starting Part 2.

The objective in this post is to work with Chores. We will list chores and then toggled their status to either deactivate them or activate them. You will need to have a few chores already created to get the most our of the examples. If you do not have any, create some basic ones linked to a process like your Save Data process.

Creating a new Chore sheet

Open your workbook from Part 1 and add a new sheet.

In row 7, add the headings for the Chores and related properties we are expecting to be returned, per below:
ID, StartTime, DSTSensitive, Active, ExecutionMode, Frequency, Attributes

Add a named range for cell A8 as Chores. This is just a place holder to return the list of chores to without hard-coding a cell.

Don’t worry about adding buttons just yet. We will do that in the next steps.

Perform the relevant formatting and save your changes.

Listing the Chores

We will now add a button to call some code to return the Chores.

Create the VBA code

Open the VBA editor and create a new Sub based on the code below:

				
					Sub GetChores()
 
Dim oJSON As Variant
Dim oProperty As Variant
Dim oSubProperty As Variant
Dim oSubSubProperty As Variant
Dim sResultRange As String
Dim pQueryString As String
Dim sResult As String
Dim iRow As Integer
Dim iCol As Integer

Application.EnableEvents = False
Application.ScreenUpdating = False

 
'Clear old rows
sResultRange = "Chores"
If Range(sResultRange).Value2 <> "" Then
    Range(Range(sResultRange), Range(sResultRange).SpecialCells(xlLastCell)).EntireRow.Clear
End If

 
'Set our Query string and execute it
pQueryString = "Chores"
Set oJSON = ExecuteQuery("Get", pQueryString)
 

'Unpack our JSON result
If Not oJSON Is Nothing Then
    iRow = 0
    For Each oProperty In oJSON("value")
        iCol = 0
        For Each oSubProperty In oProperty
            If VBA.VarType(oProperty(oSubProperty)) <> 9 Then
                Range(sResultRange).Offset(iRow, iCol).Value2 = oProperty(oSubProperty)
            Else
                sResult = ""
                'There can be multiple attributes so need to loop through each
                For Each oSubSubProperty In oProperty(oSubProperty)
                    sResult = sResult + oSubSubProperty + ":" + oProperty(oSubProperty)(oSubSubProperty) + "; "
                Next
                Range(sResultRange).Offset(iRow, iCol).Value2 = sResult
            End If
            iCol = iCol + 1
        Next
        iRow = iRow + 1
    Next
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
 
End Sub

Create a Button to call our procedure

Create a button on the worksheet and link it to the GetChores sub. Update the text to Get Chores per the above screenshot.

Test our procedure

Click the button to retrieve the Chores and related properties.

A list should be built from cell A8, our Chores range and return values like the screenshot above but for your chores.

Futher reading: VBA.VarType – https://docs.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/vartype-function

Toggling the Active indicator

Now that we have a list of Chores, we may want to toggle the Active indicator to either Activate or Deactivate the chore. Typically when I refresh a model from the production environment to a development environment, one of the first things I need to do is to deactivate any chores that should not be running e.g. master data and data loads.

I would however want to keep my backups activated.

Create the VBA Code

				
					Sub ChoreActivateDeactivate()

Dim oJSON As Variant
Dim pQueryString As String
Dim sChore As String
 
Application.EnableEvents = False
Application.ScreenUpdating = False

 
'Get the value in Column A, irrespective of where the active cell was on the row
sChore = ActiveCell.EntireRow.Columns(1).Value2
If sChore = "" Then GoTo Cleanup

'Return only a True or False value from the query
pQueryString = "Chores('" + sChore + "')/Active"
Set oJSON = ExecuteQuery("Get", pQueryString)

 
'Read the value from the Active property queried
If oJSON("value") = False Then
    pQueryString = "Chores('" + sChore + "')/tm1.Activate"
ElseIf oJSON("value") = True Then
    pQueryString = "Chores('" + sChore + "')/tm1.Deactivate"
Else
    GoTo Cleanup
End If

Set oJSON = ExecuteQuery("Post", pQueryString)

'Refresh Chore list to show new Active indicator
Call GetChores
 
Cleanup:
Application.EnableEvents = True
Application.ScreenUpdating = True
 
End Sub

Create a Button to call our procedure

Create a button on the worksheet and link it to the ChoreActivateDeactivate sub. Update the text to De/Activate Chore per the above screenshot.

Test our procedure

Click somewhere in the row containing the Chore that you want to Activate or Deactivate to set the context.

Click the De/Activate Chore button to execute the code and refresh the list.

You should see that the Active indicator has toggled.

Activate or Deactivate all Chores

Now that you have the basics, you should be able to create procedures that either Activate or Deactivate all chores. Again, this may be useful when refreshing a development environment from production and it is easier to deactivate all chores then activate by exception.

Summary

You should be able to create a list of Chores and interact with the Active indicator.

This is just another example of some useful time-saving tips that the REST API can facilitate.

As always, please let me know if you find this useful, find any gremlins or have other comments in general.

By George Tonkin, Business Partner at MCi.