jsonvbaautocorrect

Retrieving one value from JSON works, but identical approach on the other value fails


The source .json file is as simple as this:

{
  "rates": {
    "EURUSD": {
      "rate": 1.112656,
      "timestamp": 1559200864
    }
  },
  "code": 200
}

I can return the "timestamp" value, but using the identical approach I cannot return the "rate" value.

This runs with no problems:

Sub current_eur_usd()

  Dim scriptControl As Object
  Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
  scriptControl.Language = "JScript"
  Dim oJSON As Object

  With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "https://www.freeforexapi.com/api/live?pairs=EURUSD", False
    .send
    Set oJSON = scriptControl.Eval("(" + .responsetext + ")")
    .abort
  End With
  MsgBox oJSON.rates.EURUSD.timestamp   '<<< 'timestamp' works, 'rate' fails

  Set oJSON = Nothing
  Set scriptControl = Nothing
End Sub

But when I try to replace timestamp with rate, I get the error message highlighting the MsgBox line.

Run-time error '438':
Object doesn't support this property or method

I think the problem lies in VBA automatically capitalizing rate.

MsgBox oJSON.rates.EURUSD.rate

auto-transforms into

MsgBox oJSON.rates.EURUSD.Rate

How can I return the "rate" value?


Solution

  • A workaround could be evaluating it :

    MsgBox scriptControl.Eval("(" + .responsetext + ").rates.EURUSD.rate")
    

    The object can also be assigned to JS variable (not tested) :

    Set EURUSD = scriptControl.Eval("EURUSD = (" + .responsetext + ").rates.EURUSD")
    Debug.Print scriptControl.Eval("EURUSD.rate")
    Debug.Print EURUSD.timestamp