How to parse JSON with VBA without external libraries?

by

I’ve found this script example useful (from http://www.mrexcel.com/forum/excel-questions/898899-json-api-excel.html#post4332075 ):

Sub getData()

    Dim Movie As Object
    Dim scriptControl As Object

    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://www.omdbapi.com/?t=frozen&y=&plot=short&r=json", False
        .send
        Set Movie = scriptControl.Eval("(" + .responsetext + ")")
        .abort
        With Sheets(2)
            .Cells(1, 1).Value = Movie.Title
            .Cells(1, 2).Value = Movie.Year
            .Cells(1, 3).Value = Movie.Rated
            .Cells(1, 4).Value = Movie.Released
            .Cells(1, 5).Value = Movie.Runtime
            .Cells(1, 6).Value = Movie.Director
            .Cells(1, 7).Value = Movie.Writer
            .Cells(1, 8).Value = Movie.Actors
            .Cells(1, 9).Value = Movie.Plot
            .Cells(1, 10).Value = Movie.Language
            .Cells(1, 11).Value = Movie.Country
            .Cells(1, 12).Value = Movie.imdbRating
        End With
    End With

End Sub

More Related Contents:

  1. Parsing JSON in Excel VBA
  2. Why is JSON invalid if an integer begins with a leading zero?
  3. Returning Null JSON Throwing Type Mismatch Error in VBA
  4. Excel VBA: Parsed JSON Object Loop
  5. Convert Mongoose docs to json
  6. ASP JSON: Object not a collection
  7. In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?
  8. jq: print key and value for each entry in an object
  9. Parsing JSON feed automatically into MS Access
  10. In Excel VBA on Windows, how to get stringified JSON respresentation instead of “[object Object]” for parsed JSON variables?
  11. Post JSON to web in excel vba
  12. Handle JSON Object in XMLHttp response in Excel VBA Code
  13. Using VBA and VBA-JSON to access JSON data from WordPress API
  14. Kotlin Json Parser
  15. Is there a streaming API for JSON? [closed]
  16. How to make HTTP Post request with JSON body in Swift
  17. Convert JSON format to CSV format for MS Excel [closed]
  18. Nested ng-repeat
  19. Disable Hypertext Application Language (HAL) in JSON?
  20. Spring Security and JSON Authentication
  21. Is there any possibility to have JSON.stringify preserve functions?
  22. Post a json body with swagger
  23. How to parse json and read in vb.net
  24. Angular2 Ansychronous bootstrapping with external json configuration file
  25. Only allow properties that are declared in JSON schema
  26. Split a JSON file into separate files
  27. Converting JSON to NSData, and NSData to JSON in Swift
  28. How to access remote data and write it into a file during Nuxt build?
  29. CORS enabled but response for preflight has invalid HTTP status code 404 when POSTing JSON
  30. Did the publication of ECMA-404 affect the validity of JSON texts such as ‘2’ or ‘”hello”‘?

Leave a Comment