Loading ...
Sorry, an error occurred while loading the content.

Re: [json] Parsing JSON with VBA?

Expand Messages
  • Gaetano Giunta
    a bit out of scope, but carefully written javascript libraries can all be used this way. And there should be a lot that support json via eg. regexps (if plain
    Message 1 of 6 , Apr 4, 2008
    • 0 Attachment
      a bit out of scope, but carefully written javascript libraries can all
      be used this way. And there should be a lot that support json via eg.
      regexps (if plain eval sounds too insecure)

      Gaetano


      > The fastest way to get started would be to load the JScript (MS's
      > JavaScript engine) and get it to parse the JSON, like this:
      >
      > Set sc = CreateObject("ScriptControl")
      > sc.Language = "JScript"
      > json = "{""x"":10,""y"":15}"
      > Set o = sc.Eval("(" + json + ")")
      > ? o.x, o.y
      > 10 15
      >
      > You can just fire up Excel or Word and try the above code out in the
      > VBA IDE's immediate window.
      >
      > Hope this helps,
      > Atif
      >
      > From: json@yahoogroups.com <mailto:json%40yahoogroups.com>
      > [mailto:json@yahoogroups.com <mailto:json%40yahoogroups.com>] On
      > Behalf Of Bill Guindon
      > Sent: Friday, April 04, 2008 5:37 AM
      > To: json@yahoogroups.com <mailto:json%40yahoogroups.com>
      > Subject: [json] Parsing JSON with VBA?
      >
      > I'm looking for a script/example/library written in VBA to parse JSON
      > data. I didn't see one on json.org, and have had little luck with
      > Google searches. Anybody know of one?
      >
      > If nothing else, the next VBA guy will at least find a hit when
      > searching this group.
      >
      > Thanks, in advance, for any help/pointers.
      > --
      > Bill Guindon (aka aGorilla)
      > The best answer to most questions is "it depends".
      >
      > [Non-text portions of this message have been removed]
      >
      >



      [Non-text portions of this message have been removed]
    • Atif Aziz
      ... Right and so one could leverage json2.js like this: Set sc = CreateObject( ScriptControl ) sc.Language = JScript Set xhr =
      Message 2 of 6 , Apr 4, 2008
      • 0 Attachment
        > but carefully written javascript libraries can all
        > be used this way.
        > And there should be a lot that support json via eg.
        > regexps (if plain eval sounds too insecure)

        Right and so one could leverage json2.js like this:

        Set sc = CreateObject("ScriptControl")
        sc.Language = "JScript"

        Set xhr = CreateObject("Microsoft.XMLHTTP")
        xhr.open "GET", "http://www.json.org/json2.js", False
        xhr.send
        sc.AddCode(xhr.responseText)

        json = "{""x"":10,""y"":15}"
        Set o = sc.Eval("JSON.parse('" + json + "')")
        ? o.x, o.y

        The bit in the middle downloads json2.js and adds it to the engine, much like a browser would. From there on, one could call JSON.parse to add a safety net.

        - Atif

        From: json@yahoogroups.com [mailto:json@yahoogroups.com] On Behalf Of Gaetano Giunta
        Sent: Friday, April 04, 2008 3:18 PM
        To: json@yahoogroups.com
        Subject: Re: [json] Parsing JSON with VBA?

        a bit out of scope, but carefully written javascript libraries can all
        be used this way. And there should be a lot that support json via eg.
        regexps (if plain eval sounds too insecure)

        Gaetano

        > The fastest way to get started would be to load the JScript (MS's
        > JavaScript engine) and get it to parse the JSON, like this:
        >
        > Set sc = CreateObject("ScriptControl")
        > sc.Language = "JScript"
        > json = "{""x"":10,""y"":15}"
        > Set o = sc.Eval("(" + json + ")")
        > ? o.x, o.y
        > 10 15
        >
        > You can just fire up Excel or Word and try the above code out in the
        > VBA IDE's immediate window.
        >
        > Hope this helps,
        > Atif
        >
        > From: json@yahoogroups.com <mailto:json%40yahoogroups.com>
        > [mailto:json@yahoogroups.com <mailto:json%40yahoogroups.com>] On
        > Behalf Of Bill Guindon
        > Sent: Friday, April 04, 2008 5:37 AM
        > To: json@yahoogroups.com <mailto:json%40yahoogroups.com>
        > Subject: [json] Parsing JSON with VBA?
        >
        > I'm looking for a script/example/library written in VBA to parse JSON
        > data. I didn't see one on json.org, and have had little luck with
        > Google searches. Anybody know of one?
        >
        > If nothing else, the next VBA guy will at least find a hit when
        > searching this group.
        >
        > Thanks, in advance, for any help/pointers.
        > --
        > Bill Guindon (aka aGorilla)
        > The best answer to most questions is "it depends".
        >
        > [Non-text portions of this message have been removed]
        >
        >

        [Non-text portions of this message have been removed]
      • Matthew Morley
        In the past I tried this within VB6 apps and using the script object seemed to cause Norton Anti-virus throw scripting alerts and halt execution until the user
        Message 3 of 6 , Apr 4, 2008
        • 0 Attachment
          In the past I tried this within VB6 apps and using the script object seemed
          to cause Norton Anti-virus throw scripting alerts and halt execution until
          the user allowed it. Unfortunately it was a once-per application session
          type message with no way to `always allow`. In a distributed enterprise
          environment it was a scary concept, so we used a crippled json format that
          was simpler to parse.

          Never was able to pin it down, but it was reproducible and killed json as
          possibility for that project. I don't run Norton anymore so I cannot confirm
          if this is still the case, but wanted to share as something to check on. You
          can probably find my old posts on the topic. Hopefully the issue has gone
          away.

          I have not seen a vb version of the parser, but based on the regex-less one
          posted recently, it should not be too bad to write if really needed.

          --
          Matt (MPCM)


          [Non-text portions of this message have been removed]
        • Bill Guindon
          Thanks to all who replied, we ll be experimenting with the suggestions. ... That would be a problem, in the long run, we re looking to ship this to customers.
          Message 4 of 6 , Apr 4, 2008
          • 0 Attachment
            Thanks to all who replied, we'll be experimenting with the suggestions.

            On 4/4/08, Matthew Morley <WickedLogic@...> wrote:
            >
            > In the past I tried this within VB6 apps and using the script object seemed
            > to cause Norton Anti-virus throw scripting alerts and halt execution until
            > the user allowed it. Unfortunately it was a once-per application session
            > type message with no way to `always allow`. In a distributed enterprise
            > environment it was a scary concept, so we used a crippled json format that
            > was simpler to parse.
            >
            > Never was able to pin it down, but it was reproducible and killed json as
            > possibility for that project. I don't run Norton anymore so I cannot confirm
            > if this is still the case, but wanted to share as something to check on. You
            > can probably find my old posts on the topic. Hopefully the issue has gone
            > away.

            That would be a problem, in the long run, we're looking to ship this
            to customers.

            > I have not seen a vb version of the parser, but based on the regex-less one
            > posted recently, it should not be too bad to write if really needed.

            Any chance you have a link to that?

            > --
            > Matt (MPCM)
            >

            --
            Bill Guindon (aka aGorilla)
            The best answer to most questions is "it depends".
          Your message has been successfully submitted and would be delivered to recipients shortly.