Rest Results Explorer

Page Content hide
1 What can you learn here?
2 How to get rest API results into a treeview get it now
3 Example rest library explorer session
4 How this works.
5 Here’s the code

What can you learn here?

  • Populate a treeview
  • do REST queries interactively
  • explore the results

How to get rest API results into a treeview get it now

In Create a treeview from json we looked at how to load up a treeview with a cJobject. One of the examples loaded up the Rest to Excel library to a treeview. Taking it one stage further, what if we executed the rest queries right there, in the excel form, and loaded the results into a treeview – well here it is.

Note
I’ve had to suspend this one from the downloadable worksheet because Windows 7 service pack 1 and windows 8 seem to have destroyed the treeview control required for this function. I will reinstate once I find a suitable alternate control to use
Try it out first ? These are enhancements to Data Manipulation Classes so they have been included in cDataSet.xlsm, which is downloadable from Download Complete Projects. You can find the test tab, restLibraryForm, and run this right away.

Example rest library explorer session

On bringing up the form, you will see the rest Library contents.

You should

  • Select a library entry. To add new entries to the library, see Adding to the rest library. Alternatively you can enter a URL directly into the the Query URL text box.
  • Enter some Query Text. In this case we are going to use Yahoo geocode library entry to geocode against a UK postcode.
  • Execute the Query

You’ll get this

Which you can use to make sense of the results that come back from the API so you can continue to extend your Rest to Excel library

Clear Query to get back to the Rest Library.

How this works.

I already covered in Create a treeview from json the simplicity of creating a treeview from a cJobject. Rather nicely, the Rest library class cRest. also returns a cJobject containing the results of its query, so using the cJobject.toTreeView() method, we can use exactly the same technique to populate the treeview as we did the library.

Here’s the code

 
Option Explicit
Private Sub cbExecute_Click()
    Dim cr As New cRest, dSet As New cDataSet, cj As cJobject
    Set cj = getRelatedCjobject
    If (Not cj Is Nothing) Then
        Set cr = restQuery(, , tbText.text, , tbURL.text, , cj.child("treeSearch").toString = "True", _
                False, False, , True)
        If Not cr Is Nothing Then
            cbExecute.Caption = "Clear Query"
            trcJobject.Nodes.clear
            cr.jObject.toTreeView trcJobject
        End If
    Else
        cbExecute.Caption = "Execute Query"
        trcJobject.Nodes.clear
        userformExampleRestLibrary
    End If
End Sub
Private Function getRelatedCjobject() As cJobject
    Dim s As String, n As Long
    ' bit of a hack.. need to get rid of the root
    If Not trcJobject.SelectedItem Is Nothing Then
        n = InStr(1, trcJobject.SelectedItem.key, ".")
        s = Mid(trcJobject.SelectedItem.key, n + 1)
        Set getRelatedCjobject = createRestLibrary().child(s)
    End If
End Function
Private Sub trcJobject_Click()
    Dim cj As cJobject
    Set cj = getRelatedCjobject
    'now we should have the right entry
    If (Not cj Is Nothing) Then
        If (Not cj.child("url") Is Nothing) Then tbURL.text = cj.child("url").toString
    End If
End Sub
Private Sub UserForm_Initialize()
    userformExampleRestLibrary
End Sub
Private Sub userformExampleRestLibrary()
    createRestLibrary().toTreeView trcJobject
End Sub

See How to traverse a treeview and Getting started with recursion for a gentle introduction into the recursion technique used here.

Now see more about Rest to Excel library.