Print from an object to a Sheet with VBA-CodePudding

I'm totally new to VBA and i never coded anything similar. Anyway i'm not having any troubles with the logic and the code itself so i'm gonna post just a piece of my code to explain my question. I have this two For that get my data from an API and then cleans them. So if i execute this i can have the variable jsonRow as Object and i can extract the singles values from it.

Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Dim page As String
Dim per_page As String
Dim Rows() As String

page=3
For i = 1 To page
    Set objRequest = CreateObject("MSXML2.XMLHTTP")
    strUrl = "https://***"
    
    With objRequest
        .Open "GET", strUrl, blnAsync
        .SetRequestHeader "oauth-token", "***"
        .SetRequestHeader "hostname", "***"
        .SetRequestHeader "x-client-domain", "***"
        .SetRequestHeader "Content-Type", "application/json"
        .Send
        
        While objRequest.readyState <> 4
            DoEvents
        Wend
        strResponse = .ResponseText
    End With
    
    Rows = Split(strResponse, "},", per_page   1)
    GetArrayLength = UBound(Rows) - LBound(Rows)
    
    For x = 0 To GetArrayLength - 1
        Rows(0) = Replace(Rows(0), "{""data"":[", "")
        Rows(per_page - 1) = Split(Rows(per_page - 1), "}]", 2)(0)
        Rows(x) = Rows(x) & "}"
        'Rows(x) = Replace(Rows(x), "\", "")
        
        
        Set jsonRow = JsonConverter.ParseJson(Rows(x))

        Debug.Print Rows(x)
        Debug.Print jsonRow("numero_commessa")
        

    Next
    
Next

So as output i'll get something like:

Rows(x)

{"numero_commessa":"A00008","stato":"Aperta","tiratura":0,"isbn":"***","gredit":"80A","title":"VETTESE E AL*ART VISION A","dtcreate":"20\/07\/2021","type":"RISTAMPA","dtcons":"20\/07\/2021"}

and jsonRow("numero_commessa")

A00008

What i need to do, now is "print" those data on a sheet (or even better in a table on a sheet) but i can't understand how. I can only founds examples that takes data from some sheet cells and copy that on other cell's. So for every X i got an object and i need to put the values of this object on a different sheet row, on different columns of course. Something like that:

I solved by using "Range" in the For x=0 loop.

Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Dim page As String
Dim per_page As String
Dim Rows() As String

page=3
For i = 1 To page
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://***"

With objRequest
    .Open "GET", strUrl, blnAsync
    .SetRequestHeader "oauth-token", "***"
    .SetRequestHeader "hostname", "***"
    .SetRequestHeader "x-client-domain", "***"
    .SetRequestHeader "Content-Type", "application/json"
    .Send

    While objRequest.readyState <> 4
        DoEvents
    Wend
    strResponse = .ResponseText
End With

Rows = Split(strResponse, "},", per_page   1)
GetArrayLength = UBound(Rows) - LBound(Rows)

For x = 0 To GetArrayLength - 1
    Rows(0) = Replace(Rows(0), "{""data"":[", "")
    Rows(per_page - 1) = Split(Rows(per_page - 1), "}]", 2)(0)
    Rows(x) = Rows(x) & "}"
    'Rows(x) = Replace(Rows(x), "\", "")


    Set jsonRow = JsonConverter.ParseJson(Rows(x))
        
        Range(Cells(Counter, 1), Cells(Counter, 1)).Value = jsonRow("numero_commessa")
        Range(Cells(Counter, 2), Cells(Counter, 2)).Value = jsonRow("stato")
        Range(Cells(Counter, 3), Cells(Counter, 3)).Value = jsonRow("tiratura")
        Range(Cells(Counter, 4), Cells(Counter, 4)).NumberFormat = "0"
        Range(Cells(Counter, 4), Cells(Counter, 4)).Value = jsonRow("isbn")
        Range(Cells(Counter, 5), Cells(Counter, 5)).Value = jsonRow("gredit")
        Range(Cells(Counter, 6), Cells(Counter, 6)).Value = jsonRow("title")
        Range(Cells(Counter, 7), Cells(Counter, 7)).Value = jsonRow("dtcreate")
        Range(Cells(Counter, 8), Cells(Counter, 8)).Value = jsonRow("type")
        Range(Cells(Counter, 9), Cells(Counter, 9)).Value = jsonRow("dtcons")
            
        Counter = Counter   1


    Next

Next

i don't really like my code, specially my solution, but this is my first time with VBA, so if you have any other ideas or tips please correct it.

CodePudding user response：

Change the format of specific range,

Range("A1").NumberFormat = "0.00"

CodePudding user response：

I almost solved using "Range" in this way inside the For x=0:

For x = 0 To GetArrayLength - 1
        Rows(0) = Replace(Rows(0), "{""data"":[", "")
        Rows(per_page - 1) = Split(Rows(per_page - 1), "}]", 2)(0)
        Rows(x) = Rows(x) & "}"
        'Rows(x) = Replace(Rows(x), "\", "")
        'Debug.Print Rows(x)
        
        Set jsonRow = JsonConverter.ParseJson(Rows(x))
        
        Range(Cells(Counter, 1), Cells(Counter, 1)).Value = jsonRow("numero_commessa")
        Range(Cells(Counter, 2), Cells(Counter, 2)).Value = jsonRow("stato")
        Range(Cells(Counter, 3), Cells(Counter, 3)).Value = jsonRow("tiratura")
        Range(Cells(Counter, 4), Cells(Counter, 4)).Value = jsonRow("isbn")
        Range(Cells(Counter, 5), Cells(Counter, 5)).Value = jsonRow("gredit")
        Range(Cells(Counter, 6), Cells(Counter, 6)).Value = jsonRow("title")
        Range(Cells(Counter, 7), Cells(Counter, 7)).Value = jsonRow("dtcreate")
        Range(Cells(Counter, 8), Cells(Counter, 8)).Value = jsonRow("type")
        Range(Cells(Counter, 9), Cells(Counter, 9)).Value = jsonRow("dtcons")
            
        Counter = Counter   1
    Next

Now the only problem that i have is the isbn is a a string made with 13 numbers but excel show it in a weird way. So "9788826817651" become 9,78883E 12 on the sheet. can anyone helps me with that?

Also, i don't really like my code and specially my solution. Any tips?

This is a sample of my sheet: