How to fix error related to JSON parse in VBA

44 views Asked by At

I am new to using vba/macro and trying to link data from FRED. I will shamelessly admit, I got the codes from chatgpt. I tried running it but got a message saying "can't execute code in break mode" and "Set xmlDoc = JsonConverter.ParseJson(xmlHttp.responseText)" seems to be the error. I've never written a code before so if you guys can explain how to fix it in plain language that would be super helpful. Thanks!

Sub GetDataFromFRED()
    Dim myURL As String
    Dim xmlHttp As Object
    Dim xmlDoc As Object
    Dim dataRange As Range

    'Specify the FRED URL for the data you want to retrieve
    myURL = "https://api.stlouisfed.org/fred/series/observations?series_id=GNPCA&api_key=6b1194d42ad5b4543a08d7da990bacd4&file_type=json"

    'Create a new XML HTTP request object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
    xmlHttp.Open "GET", myURL, False
    xmlHttp.send

    'Parse the response as JSON
    
    Set xmlDoc = JsonConverter.ParseJson(xmlHttp.responseText)

    'Specify where you want to paste the data in Excel
    Set dataRange = ThisWorkbook.Sheets(1).Range("A1")

    'Paste the data into Excel
    dataRange.Value = xmlDoc("observations")(1)("value")
End Sub

Googled the error message, JASON error related.

0

There are 0 answers