CODEWITHSUNDEEP
jsonexcelvba
user14286902
user14286902

Reputation:

Parsing JSON into Excel but having an Error

I have been using below code to convert the data from JSON to Excel but below JSON format is not converting into excel and having an error Run time error: Invalid procedure call or argument on the line ws.Cells(r, "C").Value = JSON("sku")

Here is my code that i have been using. I do not know why the error is appearing when it works for other JSON format instead of this one.

Your help will be appreciated.

    Dim objRequest  As Object
    Dim strUrl      As String
    Dim blnAsync    As Boolean
    Dim strResponse As String
    Dim idno        As Long
    Dim ws          As Worksheet
    Dim JSON        As Object
    Dim lrow As Long
    
    Set ws = Sheet4
    
    lrow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
    
    Set objRequest = CreateObject("MSXML2.XMLHTTP")
    strUrl = "url"
    blnAsync = True
    
    With objRequest
        .Open "GET", strUrl, blnAsync
        .setRequestHeader "Content-Type", "application/json"
        .send
        
        While objRequest.readyState <> 4
            DoEvents
        Wend
        
        strResponse = .ResponseText
    End With
    
    Set JSON = ParseJson(strResponse)
    
'    Debug.Print strResponse
            r = 2
            ws.Cells(r, "C").Value = JSON("sku")
            'r = r + 1

Upvotes: 0

Views: 528

Answers (1)

Raymond Wu
Raymond Wu

Reputation: 3387

I'm not sure if there is a option for this in JSONConverter but as far as I know, it doesn't like to parse JSON string that starts with a collection so I would usually create a key manually so that it will convert properly.

Below example also shows how you can loop through the collection and get the value for sku:

'.... Continue after you post the API...
    
    Dim strResponse As String
    strResponse = .responseText
    
    Dim resultDict As Object
    Set resultDict = ParseJson("{""result"":" & strResponse & "}")
    
    Dim i As Long
    Dim resultNum As Long
    resultNum = resultDict("result").Count
    
    For i = 1 To resultNum
        Debug.Print resultDict("result")(i)("id")
        Debug.Print resultDict("result")(i)("sku")
        Debug.Print resultDict("result")(i)("upc")
        

        'Loop through skuList collection
        Dim j As Long
        For j = 1 To resultDict("result")(i)("skuList").Count
            Debug.Print vbTab & resultDict("result")(i)("skuList")(j)("id")
            Debug.Print vbTab & resultDict("result")(i)("skuList")(j)("sku")
            Debug.Print vbTab & resultDict("result")(i)("skuList")(j)("skuTitle")
        Next j
    Next i

Upvotes: 1

Related Questions