I'm working on vba project to hit api (get request) with key and get the response as json and paste it in excel. I'm finding it difficult to split and paste in next line of excel sheet. Can anyone please educate how to achieve this?
Attached 2 images with expected output.
Code
Sub restAPICall()
Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Dim json As Object
Dim i As Integer
Dim rw, cl As Integer
rw = 2: cl = 1
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = ""
blnAsync = True
With objRequest
.Open "GET", strUrl, blnAsync
.SetRequestHeader "apikey", ""
.send
While objRequest.readyState <> 4
DoEvents
Wend
strResponse = .responsetext
jsontext = VBA.Split(strResponse, ",")
Do Until
For i = LBound(jsontext) To UBound(jsontext)
'Sheet1.Cells(rw, cl).Value = VBA.Replace(VBA.Replace(VBA.Right(jsontext(i), Len(jsontext(i)) - VBA.InStr(1, jsontext(i), ":")), "}", ""), """", "")
Sheet1.Cells(rw, cl).Value = VBA.Replace(VBA.Replace(VBA.Replace(VBA.Replace(VBA.Replace(VBA.Replace(VBA.Right(jsontext(i), Len(jsontext(i)) - VBA.InStr(2, jsontext(i), ":")), Chr(34), ""), "[", ""), "]", ""), "{", ""), "}", ""), "sv_id:", "")
cl = cl + 1
Next i
cl = 1
rw = rw + 1
Loop
End With
End Sub
Display More