JSON import to Excel

Question

Is it possible to script JSON calls in a macro?

I want to get a JSON string through an API connection. It looks like the problem is Excel expects the parameters to be passed in the HTML-string, but JSON passes parameters in the HTML body. Any ideas?

Answer 1

Since this is VBA, I'd use COM to call xmlhttprequest but use it in synchronous manner as not to upset VBA’s single threaded execution environment, A sample class that illustrates a post and get request in this manner follows:

'BEGIN CLASS syncWebRequest

Private Const REQUEST_COMPLETE = 4

Private m_xmlhttp As Object
Private m_response As String

Private Sub Class_Initialize()
    Set m_xmlhttp = CreateObject("Microsoft.XMLHTTP")
End Sub

Private Sub Class_Terminate()
    Set m_xmlhttp = Nothing
End Sub


Property Get Response() As String
    Response = m_response
End Property

Property Get Status() As Long
    Status = m_xmlhttp.Status
End Property

Public Sub AjaxPost(Url As String, Optional postData As String = "")
    m_xmlhttp.Open "POST", Url, False
    m_xmlhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    m_xmlhttp.setRequestHeader "Content-length", Len(postData)
    m_xmlhttp.setRequestHeader "Connection", "close"
    m_xmlhttp.send (postData)
    If m_xmlhttp.readyState = REQUEST_COMPLETE Then
        m_response = m_xmlhttp.responseText
    End If
End Sub

Public Sub AjaxGet(Url As String)
    m_xmlhttp.Open "GET", Url, False
    m_xmlhttp.setRequestHeader "Connection", "close"
    m_xmlhttp.send
    If m_xmlhttp.readyState = REQUEST_COMPLETE Then
        m_response = m_xmlhttp.responseText
    End If
End Sub

'END CLASS syncWebRequest   

So now you can call the above to return you the server's response:

Dim request As New syncWebRequest
request.ajaxGet "http://localhost/ClientDB/AllClients?format=json" 
Dim json as string 
json = request.Response

The problem here is we want to be able to read the data returned from the server in some way, more so than manipulating the JSON string directly. What's worked for me is using the VBA-JSON (google code export here) COM type Collection to handle JSON arrays and Dictionary to handle members and their declarations, with a parser factory method Parse that basically makes creating these collections of dictionaries much simpler.

So now we can parse the JSON:

[{"Name":"test name","Surname":"test surname","Address":{"Street":"test street","Suburb":"test suburb","City":"test city"}}]

into something like the following:

Set clients = parser.parse(request.Response)
For Each client In clients
    name = client("Name")
    surname = client("Surname")
    street = client("Address")("Street")
    suburb = client("Address")("Suburb")
    city = client("Address")("City")
Next

That's nice but what about stuff like being able to edit and post back the data? Well there's also a method toString to create a JSON string from the above [Collection/Dictionary] JSON data, assuming the server accepts JSON back.

Answer 2

I wrote a .NET Excel-Addin for this. It's a generic Excel JSON client that streams any JSON object straight into Excel via http.

Docs and installation instructions can be found here: http://excel-requests.pathio.com/en/master/

And here's the GitHub link: https://github.com/ZoomerAnalytics/excel-requests