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

Quadrivium

See #34965669 – Upwards

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.

Upwards

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.

Gilliland

Answer 3

You can study the answer here and then look up VBA.CVRAPI which contains all necessary Json modules to retrieve data from a URL. Though created for another purpose, the Json modules are generic and can easily be reused.

The demo form included demonstrates this. You should be able to adopt it to your URL for a test.

Lapp