Does anyone have an example showing the use of Access vba to request data from a website and parse the response that is formatted as JSON? Have you got a working solution that you can share?
Just trying to find out who may be using this combination and willing to assist others on the forum.
Also trying to determine if this is a subject that others have real interest in.
Allowing Access & JSON to work together with a new dll (MSJSON.dll) has been suggested in the Access user voice forum
Give us a new dll - MSJSON.dll
Currently to work with XML objects from API calls we have an object model from referencing MSXML6.dll that make iterating through the response easy. With JSON we can only get strings of data that we have interpret. If you created MSJSON.dll for us we could manipulate JSON data as easily as XML and make life easier for developers dealing with API calls.
my impression is that JSON seems to be coming more popular as an output from websites, so think it is likely to receive more interest as time goes on. To date, it has not been an issue for me, but I'm starting to get questions around importing JSON files so I have a watching interest rather than any practical solutions at this time.
Colin,
I did some searching last night and found an "Excel Liberation" area that I believe is
hosted/answered/moderated by BMcpher. He (Bruce) seemed to be the guru, but it was less than readable to me anyway[I do acknowledge my not having familiarity with javascript]. A lot of it involved script control and javascript. I don't know javascript and did not find examples that were easy to read --I have to do some research and learning obviously. I saw reference to cjObject?? and a few others (jsObj) I didn't recognize.
It seems to me, and I admit to be extremely novice re JSON and javascript, that you can not easily see/find/retrieve the "field names" in a json formatted string. It seems strange that there are so few examples and/or tutorials that show accessing a website/service, submitting a request, getting a json response and being able to easily understand the results of functions/methods such as json.parse or json deserialize.
I'm sure others, using Access and vba, have encountered JSON and have found solutions. I have seen many references to MS Script Control and using javascript with vba. It's difficult to just find more about javascript functions and a library that can be referenced with Access that gives accessibility to "proven javascript" methods/functions like serialize, parse, deserialize. Seem it would make an excellent set of tutorials/videos, if only we had the interest to someone familiar with all these pieces.
And, restating that I am not familiar with javascript or json, there may be technical roadbloacks to all of this of which I am unaware. I don't mean to make this trivial --it may not be practical/feasible.
Similarly, if there is a vote on UserVoice for a dll to "handle json from vba", it would seem others -familiar with vb.net or C# - may already have built such an animal that could be referenced and used with Access vba (and other office products).
Here are a couple of links I found in this general area:
The first showing a javascript function and usage.( I adjusted this to not use the excel specifics)
Code:
Sub JavaScript_in_VBA()
'Go to Menu -> Tool -> References -> Microsoft Script Control 1.0 & Enable by Clicking it
Dim jsObj As New ScriptControl
Dim InputValue1 As Integer, InputValue2 As Integer, Result As Integer
'Get Sample Parameters
'InputValue1 = ThisWorkbook.Sheets(1).Cells(1, 1)
'InputValue2 = ThisWorkbook.Sheets(1).Cells(1, 2)
InputValue1 = InputBox("Enter first number", , 1)
InputValue2 = InputBox("Enter second number", , 2)
'Define Scripting Language
jsObj.Language = "JScript"
With jsObj
'Add JavaScript Code to Script Control Object
.AddCode "function xProduct(a,b) {return (a*b);}"
'Execute Added Script & Get result
Result = .Run("xProduct", InputValue1, InputValue2)
' Result = .Eval("xProduct (1,2)")
Debug.Print Result
MsgBox Result
End With
'ThisWorkbook.Sheets(1).Cells(1, 3) = Result
End Sub
'==========================
This second one uses a specified Google Maps api. It sends a request to the Google service using xmlhttp, and gets a response as a JSON formatted string. Within the vba code, it uses predefined Google javascript functions (getAddressLine, getGeodata, getObjLength) to get information from the json response.
Here is a link to info on getAddressLine (Note:I just found some of this so where it fits in overall context is very unclear. Or whether it really applies --I haven't found anything specific to javascript ===the link I gave suggests it's java??)
Code:
'code below from google groups
'https://groups.google.com/forum/#!searchin/excel-ramblings/vba$20json%7Csort:relevance/excel-ramblings/F1B49-k962c/ynT58WOnlrgJ
'
Sub GeoLocate()
Dim jsObj As Object
Dim x As Long
Dim strJson As String
With CreateObject("msxml2.xmlhttp")
.Open "GET", "http://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&sensor=false", False
.Send
strJson = .responseText
Debug.Print strJson
End With
With CreateObject("ScriptControl")
.Language = "JScript"
.AddCode "function getAddressLine(json, line, prop) { return json.results[0]['address_components'][line][prop]; }"
.AddCode "function getGeoData(json, prop) { return json.results[0]['geometry']['location'][prop]; }"
.AddCode "function getObjLength(json, prop) { return json.results[0][prop].length; }"
Set jsObj = .Eval("(" & strJson & ")")
For x = 1 To .Run("getObjLength", jsObj, "address_components") - 1
Debug.Print .Run("getAddressLine", jsObj, x, "long_name")
Debug.Print .Run("getAddressLine", jsObj, x, "short_name") 'jdraw
Next x
Debug.Print "Lat - " & .Run("getGeoData", jsObj, "lat")
Debug.Print "Lng - " & .Run("getGeoData", jsObj, "lng")
End With
End Sub
Yes its the same site though I haven't had time to look at it properly.
My knowledge of JavaScript and JSON is possibly less than yours but its one of those things I feel I ought to learn.
As always, I will do so when something triggers a need for me to do so.
Recently I incorporated text and voice messaging capability into one of my databases using APIs provided by Twilio. I started a thread on this back in Feb.
Their website is full of example scripts using e.g Ruby, Python, JSON and other things that meant little to me but also some in VBA.
That may be an area that could provide a learning point?
I generated a 100 random record json file and asked for 1 to 3 email addresses and 0 to 4 ipAddresses in order to get a "semi complex" json file with objects and arrays.
I downloaded the product from youtube (30 day trial).
It created a new table, and added the 100 records. It did put the array data into a single field. Not unexpected. But it did leave the json syntax eg "["value",...]" so it would be easy to split those records into a new table.
It processed the 100 records very quickly. It built me a new database and table. However, it was a .mdb (C:\Users\mellon\Documents\SampleJsonToAccess.mdb)
The json file is C:\Users\mellon\Documents\Json100.json, BUT I had to change extension to .txt to attach it (.json not acceptable)
It really shows that something can be done --it's far from perfect, but a start. The website and contact info doesn't seem to work?? I'd like to hear more about it--what is behind the scene. I don't want them to give away the code, but I'd like to know the technology involved(since there is little info available generally). What does "support" mean in real terms?
I have data (attached) (You can pass the json data thru a viewer to see the structure. I use Notepad++ with json plugin.
The json file and the resulting database with table.
The program also saves session , but uses a .jif extension???
Many thanks for the You Tube link - that's a neat addin - pity its not free!
I'm still trying to get my head around all of this but its beginning to look very promising.
I don't know whether you watched the next couple of videos but this one was very interesting:
I took your json file and imported it into Excel 2010 using Import From xml on the PowerQuery menu.
Its even easier using 2016 as you just select Import from JSON
I followed the explanation in the video and within a few seconds had the attached Excel file.
It has several records for each person as I chose to expand the email & IP Address fields into separate records. So 100 records is now 456
Alternatively, you can choose to keep them as the dreaded multivalue fields
From there it was trivial to import the data into Access
Both files attached
The best part was I didn't need to understand anything at all - just do it.
Now if the process can be automated, that's $50 saved
What's really good about this site is teamwork ...
I'm not a SQLServer user nor Excel. The Excel add-in looks nice -too bad it isn't available for Access.
I modified the database by creating a function to take the multiple values in the Email and IP_Address fields and created new tables.
Here is a new version (new name)of the database with the convert function and a test routine.
Just delete the tables and you can rerun the test routine.
I'm not a SQLServer user nor Excel. The Excel add-in looks nice -too bad it isn't available for Access.
I modified the database by creating a function to take the multiple values in the Email and IP_Address fields and created new tables.
Here is a new version (same name)of the database with the convert function and a test routine.
Just delete the tables and you can rerun the test routine.
I use both SQL server and excel though I'm not as capable on either as I am in access. I need to do some more reading and video watching to improve my understanding. However after that i'll see if I can find ways of automating the JSON import into excel then access. It won't be for a week or more however.
In the meantime, I've PM'd Jon asking for XML AND JSON to be added to the allowed file types. Copied you both into that
Had a quick look at the site in your last post. Many of the features they charge for can be done for free using the 2 Excel features I wrote about in my post.
I'm sure that forum participants can resolve this -- but it may not be pretty for a few iterations. If it was real simple, it would be available and used by many. As I see it, if people can put forward some ideas/samples and constructive critique, we can get something working that can be tested and refined.
If we have some c# or vb programmers, they may be able to construct some callable routine to parse json. If such a thing exists, we haven't found a reference to it, and it's on the UserVoice as a possible.
We'll watch this and see if there is interest. It's been a background thing --I'm retired 10 years - but I see json referenced more and more. It got re-triggered here because of a post by Pesky. If he has a need, and it could be solved such that the admitted non-programmer could use it with confidence, it seems a worthwhile effort.
There certainly can be some complexity in json responses from services, but they are usually documented by the service provider.
It seems we all have a little learning to do just to be conversant with the terminology.
Correct me if I'm wrong (and I hope I'm wrong) - as I see it, a huge part of the issue is that many JSON don't stick very closely to the "standards; often very slight differences -- which would be fine if each API provided a schema to use as a template when parsing, but's that's not the case (like XML's XSD files).
There are Standards and there are Schema Generators but it seems to be that JSON doesn't provide the same Schema-support that XML does.
This is fine for a person who is only interested in parsing JSON from a sole source, but beyond that can get confusing...
Don't get me wrong -- I'm sure "we'll" get to the bottom of it one way or another!
So true. It is more akin to a written language interpreting/gathering data 'as it comes' with no real preplanning for an organised construct.
Something like jotting down notes as they occur to you, drawing arrows to link 'this thought with that thought', going back and adding a sort order etc. JSON just provides the piece of paper...
With NoSQL and relating multiple non relational datasets found on the web, I suspect it will always remain so. Ergo any JSON translator should take this into account and work with it rather than trying to force it to conform to a traditional table structure.
