Yesterday I posted an update to the data abstraction classes for Excel.
I realized that there is also a method to convert that data into a JSON string. The following code would convert a worksheet into JSON, and can be downloaded here.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Option</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Explicit</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Public</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Sub</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> mainExample</span><span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">()</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Dim</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> dSet </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">As</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> cDataSet </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Set</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> dSet </span><span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">=</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">New</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> cDataSet </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">With</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> dSet </span><span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">.</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">populateData </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Range</span><span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">(</span><span class="str" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: maroon; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">"data!$a$1"</span><span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">),</span> <span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">,</span> <span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">,</span> <span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">,</span> <span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">,</span> <span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">,</span> <span class="kwd" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: darkblue; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">True</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">If</span> <span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">.</span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Where</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Is</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Nothing</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Then</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">MsgBox</span> <span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">(</span><span class="str" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: maroon; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">"No data to process"</span><span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">)</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Else</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">MsgBox</span> <span class="pun" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">.</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">jSonObject </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">End</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">If</span><span class="pln" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;"> </span><span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">End</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">With</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">End</span> <span class="typ" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: #2b91af; font-size: 14px; vertical-align: baseline; padding: 0px; margin: 0px; border: 0px initial initial;">Sub</span> |
Here is a write up on creating jSon from Excel (and the visa versa) using this method.
https://ramblings.mcpher.com/Home/excelquirks/json/excel-json-conversion
Bruce