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

RE: [json] Parsing JSON with VBA?

Expand Messages
  • Atif Aziz
    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 =
    Message 1 of 6 , Apr 4 3:44 AM
    View Source
    • 0 Attachment
      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@yahoogroups.com] On Behalf Of Bill Guindon
      Sent: Friday, April 04, 2008 5:37 AM
      To: json@yahoogroups.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]
    • 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 2 of 6 , Apr 4 6:18 AM
      View Source
      • 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 3 of 6 , Apr 4 6:47 AM
        View Source
        • 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 4 of 6 , Apr 4 7:05 AM
          View Source
          • 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 5 of 6 , Apr 4 7:37 AM
            View Source
            • 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.