Create a treeview from almost any Excel thing in one line of code

Back to the theme of ‘do it in one line’ today.

Thinking about some the jSon – Excel capabilities on the ramblings site , and the structure of the cJobject class in which they are stored, it occurred to me that this could be a very simple way to push out data into a treeview.

Using data abstraction, we already have the capability to use SQL, make queries to REST APIs, convert worksheets to jSON and various other things all in one simple operation. Given that these operations all use the cDataSet class, and that the cDataSet class has a .jobject property, then simply creating a .toTreeView() method for the cJobject class would mean we could, for example, query an external database, load it to Excel, and throw the results up in a Treeview Control all in one go as follows

Private Sub UserForm_Initialize()
    Dim ado As New cADO
    ado.init(Range("testado!a1")).execute("tweetsentiments", _
            , "where value=1").dSet.jObject.toTreeView trcJobject
End Sub

Just create a form, add a treeview object (in this case called trcJobject), and put the above code in the form intializion procedure to execute an SQL filter on an external datasource, convert it to a cJobject, and populate the treeview object – as below. That’s all there is to it.

So now anything that can be serialized in this way – rest queries, entire spreadsheets, libraries and so on can be very quickly setup as a treeview.

For example, the excel-rest library is stored as a cJobject, and can be displayed in a treeview with just this code

createRestLibrary().toTreeView trcJobject

See Create a treeview from jSon for more details on this and to download the examples