How do you import json data from a url utilizing VBA?

Question

I have the following json array which you can easily access at the below url:

https://crowdfluttr.firebaseio.com/test/array.json

It has the following output:

{"-0p":{"date":"2015-01-01","string":"apple","value":1},"-1p":{"date":"2015-02-04","string":"banana","value":50},"-2p":{"date":"2015-02-03","string":"carrot","value":99},"-3p":{"date":"2015-02-02","string":"banana","value":20},"-4p":{"date":"2015-03-01","string":"banana","value":11},"-5p":{"date":"2015-04-01","string":"kiwi","value":23},"-6p":{"date":"2015-05-01","strawberry":"banana","value":10}}

I'd like to pull this json data from this url and then parse it to push into microsoft access.

I found resources explaining how to parse JSON (Parsing JSON, Parsing JSON in Excel VBA) but not pull it from a URL and then parseit

Answer 1

I would use XMLHTTP to download the JSON.

For parsing JSON with VBA see https://github.com/VBA-tools/VBA-JSON.

Download the ZIP file. Extract the JsonConverter.bas. Open Excel and the VBA-editor with your VBA-project. Right click the VBA-project in Project Explorer and click Import File.... Browse to the JsonConverter.bas file and import it. Make sure, you have included a reference to "Microsoft Scripting Runtime" via Tools-References.

Example using your URL:

Sub test()

 Dim httpObject As Object
 Set httpObject = CreateObject("MSXML2.XMLHTTP")

 sURL = "https://crowdfluttr.firebaseio.com/test/array.json"

 sRequest = sURL
 httpObject.Open "GET", sRequest, False
 httpObject.send
 sGetResult = httpObject.responseText

 MsgBox sGetResult

 Dim oJSON As Object
 Set oJSON = JsonConverter.ParseJson(sGetResult)

 For Each sItem In oJSON
  dItemDate = oJSON(sItem)("date")
  sItemString = oJSON(sItem)("string")
  vItemValue = oJSON(sItem)("value")

  MsgBox "Item: " & sItem & " Date: " & dItemDate & " String: " & sItemString & " Value: " & vItemValue
 Next

End Sub

This code will work for your sample JSON like:

{"-0p":{"date":"2015-01-01","string":"apple","value":1},"-1p":{"date":"2015-02-04","string":"banana","value":50}, ... }

You will have to analyze the JSON you get from httpObject.responseText to adapt the code for getting values from other JSON structures.

Answer 2

Just in case someone stumbled on this same question but needs to send parameters first before getting the responseText, you will need to tweak Axel's answer a bit.

httpObject.Open "POST", sURL, False '// instead of GET, use POST //
httpObject.SetRequestHeader "Content-Type", "Application/json" '// specify header //
httpObject.Send "{""param1"":""value1"",""param2"":""value2""}" '// pass parameter //
sGetResult = httpObject.responseText '// get response //

The next step is the same parsing of result using the functions provided above.