Is it possible in VBA convert Excel table to json
Solution 1
I'd go with modified version of this one: http://www.excelvbamacros.in/2015/01/export-range-in-jason-format.html
if you want to write it to file there's a code:
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim Fileout As Object
Set Fileout = fso.CreateTextFile("C:\some_dir\mydata.json", True, True)
Fileout.Write jsonStringFromConvertFunction
Fileout.Close
Solution 2
So I would pass in the range to a JavaScript function and let it iterate over the Excel object model and build the array in JavaScript. Then call a JavaScript library to convert array into a string (hat tip Douglas Crockford) and simply return the string to VBA. So no string operations in VBA.
The JavaScript function is given below but depends upon Douglas Crockford's library at https://raw.githubusercontent.com/douglascrockford/JSON-js/master/json2.js. Save this in a file and then amend VBA code with the correct file path so the JavaScript is loaded into the Microsoft Script Control.
function ExcelTableToJSON(rngTable) {
try {
if (rngTable && rngTable['Rows'] && rngTable['Columns']) {
var rowCount = rngTable.Rows.Count;
var columnCount = rngTable.Columns.Count;
var arr = new Array();
for (rowLoop = 1; rowLoop <= rowCount; rowLoop++) {
arr[rowLoop - 1] = new Array();
for (columnLoop = 1; columnLoop <= columnCount; columnLoop++) {
var rngCell = rngTable.Cells(rowLoop, columnLoop);
var cellValue = rngCell.Value2;
arr[rowLoop - 1][columnLoop - 1] = cellValue;
}
}
return JSON.stringify(arr);
}
else {
return { error: '#Either rngTable is null or does not support Rows or Columns property!' };
}
}
catch(err) {
return {error: err.message};
}
}
The Excel VBA code is thus
Option Explicit
'In response to
'http://stackoverflow.com/questions/38100193/is-it-possible-in-vba-convert-excel-table-to-json?rq=1
'Is it possible in VBA convert Excel table to json
'Tools->References->
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx
Private Sub Test()
Dim oScriptEngine As ScriptControl
Set oScriptEngine = New ScriptControl
oScriptEngine.Language = "JScript"
oScriptEngine.AddCode GetJavaScriptLibraryFromWeb("https://raw.githubusercontent.com/douglascrockford/JSON-js/master/json2.js")
Dim sJavascriptCode As String
sJavascriptCode = CreateObject("Scripting.FileSystemObject").GetFile("<<<Your file path to javascript file>>>\ExcelTableToJSON.js").OpenAsTextStream.ReadAll
oScriptEngine.AddCode sJavascriptCode
Dim rngTable As Excel.Range
Set rngTable = ThisWorkbook.Worksheets.Item("Sheet2").Range("A1:B2")
rngTable.Cells(1, 1) = 1.2
rngTable.Cells(1, 2) = "red"
rngTable.Cells(2, 1) = True
rngTable.Cells(2, 2) = "=2+2"
Dim sStringified As String
sStringified = oScriptEngine.Run("ExcelTableToJSON", rngTable)
Debug.Assert sStringified = "[[1.2,""red""],[true,4]]"
Stop
End Sub
Public Function GetJavaScriptLibraryFromWeb(ByVal sURL As String) As String
Dim xHTTPRequest As Object 'MSXML2.XMLHTTP60
Set xHTTPRequest = VBA.CreateObject("MSXML2.XMLHTTP.6.0")
xHTTPRequest.Open "GET", sURL, False
xHTTPRequest.send
GetJavaScriptLibraryFromWeb = xHTTPRequest.responseText
End Function
Sharunas Bielskis
I have 2 years’ experience in the using of C# programming language (more than one-year learning and near one-year different type applications creation). I also have 5 years’ experience in the development of Excel VBA programs and I used to combine C# and Excel VBA programs in systems creation. I achieved Microsoft certification "Software development fundamentals" in 10/2014. My C# programming language knowledge is close to the certificate level (I took Microsoft exam "Programming in C#" in 03/2015 and 04/2015. My score was 681 - passing score 700). In October 2019 I started preparation for Programming in C# exam again and plan to retake it soon. I use Visual Studio's latest versions for the Console, WPF, and MVC projects creation. I widely use generic List and HashSet collections and LINQ to Objects what do programs which operates with more than 3 million data rows much faster. I have the experience to work with complex data structures and to use created custom types (classes), multidimensional and jagged arrays, a list of arrays or an array of lists dependently on input data and required reports structure. Several C# applications were created for data exchange through the internet with suppliers and sales platforms Web API. In applications for work with pdf files (inserting images, additional pages, text, barcodes, hiding some existing text) was used iTextSharp library and for barcodes creation Zen.Barcode library, for faster data reading from Excel files was used ExcelDataReader library and for faster Excel reports formation EPPlus library. I like to be a part of the developers’ community. I develop Excel VBA and C# applications to a company that sells products through the world’s largest online retailers (Amazon, eBay and other) from 2016 January until now and Excel VBA modules for construction companies’ information systems from 2012 until 2014 and from April 2019 until now. I have excellent knowledge of accounting and advanced information systems implementation. I was awarded the qualification degree of a Master of Economics (With Excellence – 2005. My master's thesis was about construction companies ERP (Enterprise Resource Planning) systems) and the qualification of an Electronic Technology Engineer (1985). I have 3 years’ experience as an IT department manager (advanced software systems implementations) and 15 years of professional experience in accounting and finance. The combination of economic and technological education and experience allows me quickly to understand production and information technologies and their relationship with economical information which is needed for decision-making. I have a lot of experience to work in teams and communicate with different level managers and specialists. My English is fluent in the professional fields. I open to job proposals now.
Updated on December 15, 2020Comments
-
Sharunas Bielskis over 3 years
I need convert data from excel table with about twenty columns and a lot of rows into json. I don't found a short example of code for this purpose in vba. I found this one https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas , but it is very large. May be it is a shorter example?