How to parse Json data from webpage to excel sheet ?

[ppnar007]

Hello,

I have made this program , I wanna parse webpage json data in excel rows and columns , I am getting error
I have imported json converter from file mentioned here : "Import Json to excel and export excel to Json (Updated 2022) - Coding is Love"
Please help me to parse this data

Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")

'original url = "https://www.crisilratings.com/en/home/our-business/ratings/rating-rationale.html"

sURL = "https://www.crisilratings.com/content/crisilratings/en/home/our-business/ratings/rating-rationale/_jcr_content/wrapper_100_par/ratingresultlisting.results.json?cmd=RR&start=0&limit=100&filters={}&_=1678267659258"


http.Open "GET", sURL, False
http.send

MsgBox http.responseText


Set JSON = ParseJson(http.responseText)

' ParseJson downloaded from

i = 2
For Each Item In JSON
Sheets("Output").Cells(i, 1).Value = Item("ratingDate")
Sheets("Output").Cells(i, 2).Value = Item("abstractTemp")
Sheets("Output").Cells(i, 3).Value = Item("companyCode")
Sheets("Output").Cells(i, 4).Value = Item("companyName")
Sheets("Output").Cells(i, 5).Value = Item("heading")
Sheets("Output").Cells(i, 6).Value = Item("ratingDate")
Sheets("Output").Cells(i, 7).Value = Item("showAbstarct")
Sheets("Output").Cells(i, 8).Value = Item("transDate")
i = i + 1
Next
MsgBox ("complete")
End Sub

 

[Kerryx]

Fairly easy if you use Power query

let
    Source = Json.Document(Web.Contents("https://www.crisilratings.com/content/crisilratings/en/home/our-business/ratings/rating-rationale/_jcr_content/wrapper_100_par/ratingresultlisting.results.json?cmd=RR&start=0&limit=100&filters={}&_=1678267659258")),
    docs = Source[docs],
    #"Converted to Table" = Table.FromList(docs, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ratingDate", "companyCode", "prId", "heading", "abstractTemp", "showAbstarct", "ratingFileName", "transDate", "companyName", "industryName"}, {"Column1.ratingDate", "Column1.companyCode", "Column1.prId", "Column1.heading", "Column1.abstractTemp", "Column1.showAbstarct", "Column1.ratingFileName", "Column1.transDate", "Column1.companyName", "Column1.industryName"})
in
    #"Expanded Column1"

 

[Legacy 143009]

Rescue the json items from docs object. I came up with this solution:

 sGetResult = httpObject.ResponseText
 sGetResult = Left(sGetResult, InStrRev(sGetResult, "]"))
 sGetResult = Right(sGetResult, Len(sGetResult) - InStr(sGetResult, "[") + 1)

 

[ppnar007]

Thanks all for your precious time , I have also written program in following way and its working nicely

sURL = "https://www.crisilratings.com/content/crisilratings/en/home/our-business/ratings/rating-rationale/_jcr_content/wrapper_100_par/ratingresultlisting.results.json?cmd=RR&start=0&limit=100&filters={}&_=1678267659258"
 
 
    http.Open "GET", sURL, False
    http.send
 
    Set Json = ParseJson(http.responseText)
 
    Set c = ActiveSheet.Range("A1")
    j = 1
        For Each act In Json("docs")

            c.Resize(1, 11).Value = Array(j, act("ratingDate"), act("companyCode"), act("prId"), act("heading"), _
                                   act("abstractTemp"), act("showAbstarct"), act("ratingFileName"), act("transDate"), act("companyName"), act("industryName"))
                             
                                                        
            Set c = c.Offset(1, 0)
        j = j + 1
        Next act

 

[ppnar007]

But now I wanna tale all tables from webpage : India Ratings and Research: Most Respected Credit Rating and Research Agency India

api is like this : https://www.indiaratings.co.in/pres...61191&uniqueIdentifier=10.190.19.169-20230306

I just wanna fetch all tables but donno how to .