👀 Views: 0 💬 Answers: 1 📅 Created: 2025-06-21

vba excel json

I'm refactoring my project and I'm trying to import JSON data from a REST API into Excel using VBA, but I'm working with a 'Run-time behavior 1004: Application-defined or Object-defined behavior' when trying to write the parsed data to specific cells..... My goal is to fetch user details from the API and populate them into a worksheet. Here's the code snippet I've been using: ```vba Sub ImportJSONData() Dim http As Object Dim json As Object Dim userData As Variant Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", "https://jsonplaceholder.typicode.com/users", False http.Send If http.Status = 200 Then Set json = JsonConverter.ParseJson(http.responseText) ' Using JsonConverter Dim i As Long i = 1 For Each userData In json ws.Cells(i, 1).Value = userData("id") ws.Cells(i, 2).Value = userData("name") ws.Cells(i, 3).Value = userData("email") i = i + 1 Next userData Else MsgBox "behavior fetching data! Status: " & http.Status End If End Sub ``` I have referenced the JsonConverter module correctly, and I can confirm that the API is returning the expected JSON structure as I've tested it in Postman. However, when I run this code, I get the behavior mentioned above, specifically pointing to the line `ws.Cells(i, 1).Value = userData("id")`. I've tried using different sheet names and even simplified the JSON to just a single object, but the behavior continues. Any suggestions on how to resolve this scenario or what might be causing the question? I'm using Excel 2016 and the JsonConverter from GitHub. Thank you! The stack includes Vba and several other technologies. Any advice would be much appreciated.