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.
On bringing up the form, you will see the rest Library contents.
You should
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.
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.
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.
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines