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

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

1 Comment

Comments are closed.