I had a question on the Excel Liberation forum the other day about how to automate the creation of the input data – an extract of which looks like this
Pretty simple, but tedious to create manually if your data is not already organized that way. Using cJobject and cDataSet along with a bit of recursion makes restructuring of most datasets very simple. I’ll use a color theory visualization to demonstrate a pattern for converting data structure. The output we want is this
but the input data we want looks like this
Whereas what we need to be able to feed the d3.js conversion is something like this
Here’s a VBA pattern that will transform the data, and then call up the d3.js conversion.
How does this work?
The secret sauce here is that the .add method of cJobject , usually used for creating or decoding json, has the very handy attribute of adding a child node if it is unknown or updating the value if the key is already known. Along with its recursion friendly structure, this very concise piece of code is all that is needed to turn the first data structure into the second
Set croot = job.add(InputBox("root name"))
For Each dr In ds.rows
Set ck = croot
For Each dc In dr.columns
If Not IsEmpty(dc.value) Then
Set ck = ck.add(dc.toString)