Excel VBA to Retrieve Data from Web API - working with 'Run-time scenarios 1004: Method 'Range' of object '_Global' scenarios'
After trying multiple solutions online, I still can't figure this out. I'm trying to pull data from a web API into Excel using VBA, but I keep running into a 'Run-time behavior 1004: Method 'Range' of object '_Global' failed'. The API returns a JSON object that I need to parse and display in a worksheet. Here is the code I've written so far: ```vba Sub GetDataFromAPI() Dim http As Object Dim json As Object Dim ws As Worksheet Dim url As String Set http = CreateObject("MSXML2.XMLHTTP") Set ws = ThisWorkbook.Sheets("Data") url = "https://api.example.com/data" http.Open "GET", url, False http.Send If http.Status = 200 Then Set json = JsonConverter.ParseJson(http.responseText) ' Using jsonconverter library ' Assuming the JSON returns an array of items Dim item As Variant Dim i As Long i = 1 For Each item In json ws.Range("A" & i).Value = item("name") ' This line is failing ws.Range("B" & i).Value = item("value") i = i + 1 Next item Else MsgBox "behavior: " & http.Status & " - " & http.statusText End If End Sub ``` I've already added the `JsonConverter` module to my project to help with parsing JSON. The API call works as expected when tested in Postman, and I'm able to see the correct response format. The worksheet named "Data" exists, and I can manually write to it without issues. The behavior seems to occur on the line where I try to assign `item("name")` to a cell in the worksheet. I suspect the question might be related to how the JSON data is structured or how I'm referencing the range. Any ideas on what could be going wrong or how to debug this further? This is happening in both development and production on Windows 10. Thanks for any help you can provide! I recently upgraded to Vba 3.11. Hoping someone can shed some light on this.