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?
On the “Data” tab, from the “Get & Transform Data” section, select Get Data > From File > From JSON. You will see your computer's standard “Import” window. Here, open the folder where your JSON file is located. Double-click the file to connect it to Excel.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With