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