PreencherJsonNaSheet2

gistfile1.txt

Sub ParseJsonToSheet(jsonText As String, sheetName As String)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(sheetName)
    ws.Cells.Clear

    Dim json As String
    json = Trim(jsonText)

    ' Remove [ ]
    If Left(json, 1) = "[" Then json = Mid(json, 2)
    If Right(json, 1) = "]" Then json = Left(json, Len(json) - 1)

    ' Divide objetos: "},{"
    Dim rows() As String
    rows = Split(json, "},{")

    ' Limpa chaves de cada parte
    Dim i As Long
    For i = LBound(rows) To UBound(rows)
        rows(i) = Replace(rows(i), "{", "")
        rows(i) = Replace(rows(i), "}", "")
    Next i

    ' Extrair cabeçalhos da primeira linha
    Dim firstObject As String
    firstObject = rows(0)

    Dim pairs() As String
    pairs = Split(firstObject, ",")

    Dim headers() As String
    ReDim headers(UBound(pairs))

    Dim j As Long
    For j = LBound(pairs) To UBound(pairs)
        headers(j) = Trim(Split(pairs(j), ":")(0))
        headers(j) = Replace(headers(j), Chr(34), "") ' Remove aspas
        ws.Cells(1, j + 1).Value = headers(j)
    Next j

    ' Preencher os dados
    Dim col As Long, row As Long
    row = 2

    For i = LBound(rows) To UBound(rows)
        pairs = Split(rows(i), ",")

        For j = LBound(pairs) To UBound(pairs)
            Dim value As String
            value = Split(pairs(j), ":")(1)

            ' Remove aspas extras
            value = Replace(value, Chr(34), "")
            ws.Cells(row, j + 1).Value = value
        Next j
        row = row + 1
    Next i
End Sub