how we can extract data from json in VBA? or How to Parse JSON with VBA without using external libraries? if similar question is on your mind and you do not know the answer.
So there is no need to worry because today in this post, I am going to tell you all the above queries about VBA. If you want to know about VBA, then read this post carefully, I hope you will get the answer to your question. So let’s start now.
First of all please add reference of microsoft script control 1.0 object library.
Parse JSON with VBA without external libraries
Option Explicit Private ScriptEngine As ScriptControl Public Sub InitScriptEngineForParsing() Set ScriptEngine = New ScriptControl ScriptEngine.Language = "JScript" ScriptEngine.AddCode "function GetPropertyFromJson(jsonObj, propertyName) { return jsonObj[propertyName]; } " ScriptEngine.AddCode "function GetKeysFromObject(jsonObj) { var keys = new Array(); for (var i in jsonObj) { keys.push(i); } return keys; } " End Sub Public Function DecodeJsonStringFromApiResponse(ByVal JsonString As String) Set DecodeJsonStringFromApiResponse = ScriptEngine.Eval("(" + JsonString + ")") End Function Public Function GetPropertyFromJson(ByVal JsonObject As Object, ByVal propertyName As String) As Variant GetPropertyFromJson = ScriptEngine.Run("GetPropertyFromJson", JsonObject, propertyName) End Function Public Function GetObjectPropertyFromJson(ByVal JsonObject As Object, ByVal propertyName As String) As Object Set GetObjectPropertyFromJson = ScriptEngine.Run("GetPropertyFromJson", JsonObject, propertyName) End Function Public Function GetKeysFromObject(ByVal JsonObject As Object) As String() Dim Length As Integer Dim KeysArray() As String Dim KeysObject As Object Dim Index As Integer Dim Key As Variant Set KeysObject = ScriptEngine.Run("GetKeysFromObject", JsonObject) Length = GetPropertyFromJson(KeysObject, "length") ReDim KeysArray(Length - 1) Index = 0 For Each Key In KeysObject KeysArray(Index) = Key Index = Index + 1 Next GetKeysFromObject = KeysArray End Function Public Sub TestJsonParse() Dim JsonString As String Dim JsonObject As Object Dim Keys() As String Dim strname As Variant Dim strcountry As Variant Dim j As Variant InitScriptEngineForParsing JsonString = "{""name"": ""johny"", ""address"": { ""country"": ""USA"",""city"": ""New York City"" } }" Set JsonObject = DecodeJsonStringFromApiResponse(CStr(JsonString)) Keys = GetKeysFromObject(JsonObject) strname = GetPropertyFromJson(JsonObject, "name") Set strcountry = GetObjectPropertyFromJson(JsonObject, "address") MsgBox ("Student Name: " & strname & " country Name:" & strcountry.country) End Sub
So our use the Javascript engine to get at the information from the JSON string. First of all, there is a function to GetKeysFromObject for getting the keys of a Javascript object.when you know the Javascript object keys, the next code is to access the properties value from the object.
As we know that VBA won’t help either if the name of the key is known at run-time. So we have two methods to access a property of the object, one for access values i.e GetKeysFromObject and the other one for getting objects and arrays i.e GetObjectPropertyFromJson.
VBAparse json array
Option Explicit Private ScriptEngine As ScriptControl Public Sub InitScriptEngine() Set ScriptEngine = New ScriptControl Dim foo As Object Dim Jsonobject As Object ScriptEngine.Language = "JScript" ScriptEngine.AddCode "Object.prototype.myitem=function( i ) { return this[i] } ; " Set Jsonobject = ScriptEngine.Eval("(" + "[ 'Mark', 'Alex' ]" + ")") ' JSON array MsgBox (Jsonobject.myitem(1)) End Sub
Thanks you so much let me know if you have any query
The short form of Visual Basic for Applications is VBA. Let me tell you that VBA is an event-driven programming language from Microsoft. Which is now mainly Microsoft Office applications; Such as MS-Excel, MS-Word, and MS-Access.
It helps build customized applications and solutions that enhance the capabilities of those applications. The benefit of this feature is that we do not require to install VB on our computer, but installing Office helps us achieve our objective.
We can use VBA in all versions of Office from MS-Office 97 to MS-Office 2013. You can also experiment with more modern versions of Office that are available.
VBA Excel is the most famous programming, using VBA is that we can build a powerful tool using linear programming.
Now let’s know what VBA is used for?
Use of VBA :
VBA is used as follows:
MS-Excel provides only basic built-in functions which are not effective in complex calculations. In all these situations VBA has become the obvious solution.
It is quite difficult to calculate the monthly loan repayment using the built-in function of Excel but the VBA program for this type of calculation is simple.
Now we have learned about the use of VBA, now let us also know about its features.
Features of VBA:
Following are the features of VBA:
(1) Connectivity to the database can be done in VBA.
(2) Many difficult tasks can be simplified by programming through VBA.
(3) VBA provides an environment to work on multiple applications (eg – MS – Excel, MS – Access, etc.).
(4) VBA has built-in functions for stinging mathematical operations etc.
(5) Any user in VBA can create his own user-defined functions.
Now let us know that how many types of windows are there in VBA?
VBA Window:
To access the Visual Basic Editor in Excel is to press the Alt+F11 key .Here are some Windows that appear in the Excel VBA editor. This is controlled through the View menu at the top of the VBA editor window. The following are the names of each window:
1. Project Window
2. Code Window
3. Properties Window
4. Intermediate Window
5. Locals window
6. Watches Window
DataType in VBA
When a variable is declared, we also need to specify its data type. Because the value stored in any variable depends on its data type.
However, VBA handles your data automatically regardless of the data type. As a result, the program executes very slowly.
There are several types of VBA data types, which can be divided into two broad categories:
1) Numeric data type
2) non-numeric data type
Variables
The position in which a value is filled is given the name of that char or variable. It is known by a certain name in the program. Before a variable can be used in a program, it is necessary to define or declare it. The meaning of declaring variables is to specify their name and type.
Rules for Creating Variable
The first character in the name of the variable must be an alphabetical letter.
Spaces, commas ( , ) or any other characters in the names of variables; Like – @, $, # should not be used.
In VBA Variable names must not be greater than 255 characters.
The reserved keywords of Visual Basic should not be used in the names of variables.
The post How to parse JSON with VBA without external libraries? appeared first on Software Development | Programming Tutorials.
Read More Articles
- How to parse JSON with VBA without external libraries?
- Parse simple two dimensional JSON array in VBA without external libraries
- How can I parse json in Excel vba without using microsoft scripting runtime?
- how to write value into cell with vba code without auto type conversion?
- How to save a text file (CSV) with UTF-8 without BOM encoding in VBA (Excel)?
- Parse JSON with VBA (Access 2010)
- How to save the excel file with vba coding without opening it?
- How can I use external VBA source code with MS Access?
- How do i filter a range in another sheet with VBA without activating the sheet
- How to parse Excel's cell string with regexp in VBA macro
- How to fix and extract google CSE JSON API results with excel vba
- How to add a new word table after another table with Vba without them getting nested or linked
- VBA With obj - how to reference the obj inside the With without naming it?
- VBA Json Parse response with JsonConverter
- How to choose a radiobuttton without id with selenium webdriver and VBA
- How to parse MS Word document by style with VBA
- How to open Text File with .OpenText in VBA that starts with first column like "ID" without getting SYLK error
- how to Parse multi level json with vba?
- How to grab values from different columns in a row and write them to a single cell on a separate row without overwriting with excel vba
- VBA - How do I create an Excel sheet with the same name without a (2) at the end of the name of the Excel sheet?
- How to parse nested JSON in VBA
- How to read a Lable of another Access with VBA without opening the access
- How to make cells with multiple background colors in excel using VBA without gradients?
- JSON to EXCEL using VBA without external GitHub project or Power Query
- How verify if a data exist in a recordset with an external variable? VBA
- How to parse title from the website with VBA
- How to Import multiple XML Files into Excel with VBA without overwriting the files that have already been imported
- How to sort values with VBA without changing the formatting?
- How to sort values with VBA without changing the formatting?
- How to reference and parse Json VBA Ms Access
- Assistance with a vba project
- Copy Paste Range using LastRow
- How to close an Access database and open a newdatabase on the same instance, same user, with VBA?
- Creating Duplicate Excel Row based the values in another Excel file cell
- Excel VB Script
- Insert formatted text in word document with VBA Userform
- Entering fields for songs database in Microsoft Access
- Paste range picture to chart area for export not working
- Excel 2007 VBA Array Size Limit
- Cannot disable Excel backup settings from custom Save As dialog / VBA
- Excel VBA Using pivot table data in another workbook
- Find match and copy data
- Save in a new workbook four of five sheets which contains ActiveX controls
- Excel VBA: Having trouble understanding Loops
- Refer to multiple selected Items in a listbox in ms-access
- How do I loop across specific ranges with the row number as the variable?
- Macro's Cell Value isn't effecting sheet
- Vba Button Code error
- If Statement is not functional
- how to delete rows NOT containing #N/A