Using API Parsing JSON object in Excel VBA

Question

I have an issue with understanding Excel VBA: Parsed JSON Object Loop.

I need a solution on the below code:

Sub getPricesOnReport()

 Dim url As String: url = "http://statistics.mla.com.au/ReportApi/RunReport?ReportGuid=70587516-e17a-4065-a8aa-e3fe4c512159&FromDate=13%2F03%2F2017&ToDate=18%2F03%2F2017"
 Dim httpRequest As Object: Set httpRequest = CreateObject("MSXML2.XMLHttp")
 Dim httpResponse As Object
 Dim scriptControl As Object: Set scriptControl = createObject("MSScriptControl.ScriptControl")
 Dim XDOM As ListObject

scriptControl.Language = "JScript"

httpRequest.Open "GET", url, False
httpRequest.send

Set httpResponse = scriptControl.eval("(" + httpRequest.responseText + ")")

With Sheets("MLA")

If httpResponse.ResponseStatus <> "OK" Then
    MsgBox "Error in Response"
Else
Cells(3, 2).Value = httpResponse.ResponseDate
Cells(3, 3).Value = httpResponse.ResponseHeader
Cells(3, 4).Value = httpResponse.ResponseStatus
Cells(3, 5).Value = httpResponse.ResponseDisclaimer
'Cells(4, 2).Value = httpResponse.returnValue ' 
End If  
End With
End Sub

I am getting an error for the code

Cells(4, 2).Value = httpResponse.returnValue

though the object is available.

PFB image:

How do i modify the code to access the data?

Answer 1

In this case, Capitalization matters!

ReturnValue needs to be capitalized properly.

It may be defaulting to a "small r" when you type ReturnValue if there are other references to returnValue. (VBA is trying to be helpful by correcting the word to how you typed it before!)

In the VBA Editor:

• hit Ctrl+H.

• Enter ReturnValue for both Find What and Replace With.

• Make sure Current Project is selected, and that Match Case is unchecked.

• Click Replace All

Every occurrence of the word will be changed to the correct capitalization.