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?
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