0

This is my JSON structure:

{
    "Realtime Currency Exchange Rate": {
        "1. From_Currency Code": "AUD",
        "2. From_Currency Name": "Australian Dollar",
        "3. To_Currency Code": "USD",
        "4. To_Currency Name": "United States Dollar",
        "5. Exchange Rate": "0.70856660",
        "6. Last Refreshed": "2018-10-23 17:37:03",
        "7. Time Zone": "UTC"
    }
}

The code I'm using:

Dim scriptControl As Object

Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
scriptControl.Language = "JScript"

With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=AUD&to_currency=USD&apikey=demo", False
        .send
        scriptControl.Eval "var obj=(" & .responseText & ")"
End With

How can I access values in VBA? For example, if I want to access "5. Exchange Rate" value, what should I do?

I tried many variations using [] and (), but nothing works for me.

Thank you.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
commandos1985
  • 1
  • 1
  • 2
    I recommend this: https://github.com/VBA-tools/VBA-JSON – Sam Oct 24 '18 at 16:34
  • 1
    Take a look at [this answer](https://stackoverflow.com/a/46245469/4717755) for help in understanding how the VBA-JSON link given by @Sam structures the parsed JSON result. – PeterT Oct 24 '18 at 16:39
  • I have answered this here: https://stackoverflow.com/a/52682267/6241235 Exactly the same info I think if using either JSON parser or split function. Not scriptControl. – QHarr Oct 24 '18 at 16:54

1 Answers1

2
Debug.Print scriptControl.Eval("obj['Realtime Currency Exchange Rate']['5. Exchange Rate'];")`

...but for your own sanity and security you should not use this approach to parse JSON - instead use the VBA-JSON library

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Note that VBA-JSON is now available through [VBA-Blocks](https://www.vba-blocks.com/), which is essentially "Nuget packages for VBA", which is BLOODY AWESOME and it cannot be understated how much of a huge revolution this can be. – Mathieu Guindon Oct 24 '18 at 17:50