31

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?

Improve this question
2
  • 2
    Options for consuming json in VB/VBA: stackoverflow.com/questions/2782076/…. How are you calling the API in your VBA? xmlHTTP? Commented Nov 8, 2011 at 0:46
  • JSON is just a plain-text markup for variable arrays. There isn't anything special about JSON. It sounds like Excel is passing the information through a _GET request (.com?variable=anwser). You could look at modifying the HTML server code to read the variables from a GET request (in PHP, simply $variable = $_GET['variable'];). Commented Jan 19, 2012 at 4:21

2 Answers 2

Reset to default
42

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I can't believe this hasn't been accepted / voted before now. You solved my problem perfectly - thank you!
using theData("Name") isn't working for me - any idea why? :c I added JSONLib as a class, used Dim parser As New JSONLib (not in your code originally). Then I used Dim theData and Set theData = parser.parse(request.Response). Are you dimming clients as a collection?
Nevermind, I'm an idiot! Got confused between client and clients. To help anyone else out in the future - copy the library code into a Class Module (right click - Insert -> Class Module). Name it JSONLib - copy the code from the jsonlib module in OP's code in there. Dim parser As New JSONLib Then Set theData = parser.parse(request.Response) Make sure you are using For Each key in theData key("Name") - I was trying to pull the values from theData("Name") and was super confused.
10

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

Improve this answer

4 Comments

the example times out and there are no installation instructions.
it does work though. Note if you get error and excel opens with funny chars all over you probably installed 64-bit when you meant 32-bit.
ah ok - the whole thing is not in the best state though; when I get some time, I'll clean it up (and include a powershell script that automatically picks 32/64 bit depending on your Excel) - feel free to drop me an email bjoern.stiel (at) pathio (dot) com and I'll keep you posted
It's taken a bit longer but here's the new Addin (under the new name Excel Requests): excel-requests.readthedocs.io/en/latest. Download the powershell script and execute to install the correct version.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.