Import Json to excel and export excel to Json.JSON Javascript Object Notation is the most used data exchange format nowadays.Microsoft Excel doesnt have built in support for importing JSON to excel or exporting excel data to JSON.VBScript can also be used to create applications that run directly on a workstation running Microsoft Windows.The simplest example is a script that makes use of the.Rome because the Item property is the Dictionary objects default property.If the key is a string, the case in which you specify the.Microsoft Scripting Runtime Object Library Vba String' title='Microsoft Scripting Runtime Object Library Vba String' />VBA JSON is an excellent library for parsing JSON in VBA.Lets see how to handle JSON in Excel VBA.Getting Started. Download VBA JSON latest version from here.Extract it, open VBA code editor in excel Alt F1.Add a reference to Microsoft scripting runtime.Tools references selectImport JSON to Excel.This library provides a simple method Parse.Json to parse JSON string into a dictionary object which can be used to extract data.Lets see an example.Im using fake data from http jsonplaceholder.API service with fake Json data.Well be pulling user data from http jsonplaceholder.GET request which responds with Json data.Read more about GET requests in VBA here.This post contains everything you need to know about using the VBA Dictionary.There are tons of code examples including a real world application.Next, well parse that Json and import it to excel.Code for importing data looks like this Public Sub exceljson.Dim http As Object, JSON As Object, i As Integer. Download Marathi Bhakti Geet Of Vitthal Song . Set http Create. ObjectMSXML2.XMLHTTP. Open GET, http jsonplaceholder.False. Set JSON Parse.Jsonhttp. response.Text. For Each Item In JSON.The code below allow me to upload a file to sharepoint using an Excel VBA macro function but it requires me to first go to the sharepoint site and.Sheets1. Cellsi, 1.Value Itemid. Sheets1.Cellsi, 2. Value Itemname.Sheets1. Cellsi, 3.Value Itemusername.Sheets1. Cellsi, 4.Value Itememail. Sheets1.Cellsi, 5. Value Itemaddresscity.Sheets1. Cellsi, 6.Value Itemphone. Sheets1.Cellsi, 7. Value Itemwebsite.Sheets1. Cellsi, 8.Value Itemcompanyname.Msg. Box complete.Code explanation.First, define JSON as an object and make a GET request to JSON APIJSON data received in the response is parsed by passing it into Parse.Json method. parsed data is converted into a collection of dictionaries.Loop through the collection to get each users details and set its values to the first sheet.Running above code looks like gif below.Reading JSON from a file.In the same example above, If you want to read JSON data from a local file then you can use File.System. Object to read all text in the file and then pass it to Parse.Json method. Dim FSO As New File.System. Object. Dim Json.TS As Text. Stream.Set Json. TS FSO.Open. Text. Fileexample.For. Reading. Json.Text Json. TS. Read.All. Set JSON Parse.JsonJson. Text. Export Excel to Json.VBA JSON provides another method Convert.To. Json which can be used to convert excel data into JSON.Heres an example.Sample data with Name, Phone and Email is present in second sheet.Lets convert it into JSONCode for this looks like Public Sub exceltojson.Dim rng As Range, items As New Collection, myitem As New Dictionary, i As Integer, cell As Variant.RangeA2 A3. Set rng RangeSheets2.RangeA2, Sheets2.RangeA2. Endxl. Down use this for dynamic range.For Each cell In rng.Debug. Print cell.Value. myitemname cell.Value. myitememail cell.Offset0, 1. Value.Offset0, 2. Value.Add myitem. Set myitem Nothing.Sheets1. RangeA4.Value Convert. To.Jsonitems, Whitespace 2.Code Explanation.First, define rng as range and set it to data range.Convert. To. Json method takes a dictionary collection or array as parameter.So we should pass our data as a collection.A Dictionary is an object with keys and values just like JSON but doesnt support multiple items like arrays or collections, so we create a dictionary for each item and push it into an array or a collection.Define a dictionary and a collection, loop through the range and set each rows data into myitem.Push myitem into collection and set it to nothing, because we are using the same dictionary to add next rows data and push it to collection again.Finally pass items collection to Convert.To. Json method which returns a JSON string.Running above code looks like gif below.Export Excel to JSON file.In the same example above, If you want to export excel data to JSON file then It can be done by opening a file for output by specifying the path of the file and printing data in it.Sample code below, Running this would save a JSON file in the current workbooks folder.Public Sub exceltojsonfile.Dim rng As Range, items As New Collection, myitem As New Dictionary, i As Integer, cell As Variant, myfile As String.Set rng RangeA2 A3.Set rng RangeSheets2.RangeA2, Sheets2.RangeA2. Endxl. Down use this for dynamic range.For Each cell In rng.Debug. Print cell.Value. myitemname cell.Value. myitememail cell.Offset0, 1. Value.Offset0, 2. Value.Add myitem. Set myitem Nothing.Application. Active.Workbook. Path data.Open myfile For Output As 1.Print 1, Convert.To. Jsonitems, Whitespace 2.Export Excel to Nested JSONAbove code can be modified a bit to get a nested JSON as output.Just add dictionary in another dictionary so that it creates a nested JSON.Public Sub exceltonestedjson.Dim rng As Range, items As New Collection, myitem As New Dictionary, subitem As New Dictionary, i As Integer, cell As Variant.Set rng RangeA2 A3.Set rng RangeSheets2.RangeA2, Sheets2.RangeA2. Endxl. Down use this for dynamic range.For Each cell In rng.Debug. Print cell.Value. myitemname cell.Value. myitememail cell.Offset0, 1. Value.Offset0, 2. Value.Offset0, 3. Value.Add location, subitem.Add myitem. Set myitem Nothing.Set subitem Nothing.Sheets2. RangeA4.Value Convert. To.Jsonitems, Whitespace 2.Running above code looks like image below.Wrapping up. Read official documentation of VBA JSON here and use VBA Dictionary for Mac Support.Related articles If you have and questions or feedback, comment below.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |