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'll 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