JSON Parsing using MSScriptControl
1,194 views
Skip to first unread message
Marc McLean
Apr 14, 2014, 6:46:11 AM4/14/14
to excel-r...@googlegroups.com
Bruce - first of all, E.L. is a fantastic site. Thanks for your many wonderful contributions!
I have been working w JSON for a while in Excel. My approach was to use CreateObject("MSScriptControl.ScriptControl") and parse incoming JSON w Eval. This resulted in the obtuse JScriptTypeInfo object. It never sat quite right with me that I was able to examine this object in the VBE Watch window but could not access it from within the VBA code. So much for useful COM interfaces. However, since this was a COM object, the Watch window must be using the COM Browser object under the covers to allow me to access it. So I ended up using the TypeLib object CreateObject("TLI.TLIApplication") to browse through my JScriptTypeInfo object. I have had success with this approach - basically letting the scriptcontrol do the heavy parsing and pulling the results via COM "reflection". I am curious, did you ever explore this avenue? I am not sure how you are parsing now (do you parse the JSON as a text string?). It seems that the script engine + COM browsing might offer an alternative worth checking out. Anyhow, if you are interested, let me know and I can shoot you the code - I would love to hear your thoughts. (I tried to attach code to this post but no luck). If anyone wants to see, I can retry to attach as a text file perhaps.
I have been working w JSON for a while in Excel. My approach was to use CreateObject("MSScriptControl.ScriptControl") and parse incoming JSON w Eval. This resulted in the obtuse JScriptTypeInfo object. It never sat quite right with me that I was able to examine this object in the VBE Watch window but could not access it from within the VBA code. So much for useful COM interfaces. However, since this was a COM object, the Watch window must be using the COM Browser object under the covers to allow me to access it. So I ended up using the TypeLib object CreateObject("TLI.TLIApplication") to browse through my JScriptTypeInfo object. I have had success with this approach - basically letting the scriptcontrol do the heavy parsing and pulling the results via COM "reflection". I am curious, did you ever explore this avenue? I am not sure how you are parsing now (do you parse the JSON as a text string?). It seems that the script engine + COM browsing might offer an alternative worth checking out. Anyhow, if you are interested, let me know and I can shoot you the code - I would love to hear your thoughts. (I tried to attach code to this post but no luck). If anyone wants to see, I can retry to attach as a text file perhaps.
Marc M
bruce
May 4, 2014, 10:29:48 AM5/4/14
to excel-r...@googlegroups.com
Marc
Im sorry - though I remember reading your post, i can't recall if I replied. Apologies if I didn't.
Yes I did look at various solutions when working on JSON parsing, but i didn't want to create something that required libraries that people might not have. There are half a million views to the site per year, so I have to be prepared for all combinations of everything.
I wrote the JSON parser and stringifier from scratch, but the main thing i wanted was the pseudo-class that I now have in cJobject, which I use whenever I use VBA. Since it fairly closely maps to a JavaScript object, migration between VBA and Google Apps Script is just a translation rather than a refactor.
I would be happy to publish your article in our guest posting section if you'd like to write up this method, which might suit some.
regards
bruce
Reply all
Reply to author
Forward
0 new messages