Iterate XML with VBA and pass data to cells

1

I am now starting to mess with VBA and I have the following problem: I was able to make a code that makes a SOAP request for a webservice and returns me an XML with the electronic point marking data of a person. So far the code works quietly.

What I'm having with difficulty is how to pass the XML values to the worksheet cells in Excel.

Sample webservice response: link

My code:

linha = linhaResponse

For Each item In xmldoc.SelectNodes("//itens")
    coluna = colunaResponse

    Worksheets(response).Cells(linha, coluna) = item.SelectNode("//data")(0).Text

    For Each intervalo In item.SelectNodes("//intervalos")

        If intervalo.SelectNode("//dataHora").Text = "" Then
            coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = " "
        Else
            coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = intervalo.SelectNode("//dataHora").Text
        End If

        If Not (intervalo.SelectNode("//rep") Is Nothing) Then
            coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = " "
        Else
            coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = intervalo.SelectNode("//rep")(0).Text
        End If

        coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = intervalo.SelectNode("//tipo")(0).Text
        coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = intervalo.SelectNode("//justificativa")(0).Text
    Next intervalo

    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//chp")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//ht")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//htr")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//htn")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//hnt")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//hi")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//faltas")(0).Text

    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//banco")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//minutes")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//noturno")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//percentual")(0).Text


    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//modo")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//valor")(0).Text


    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//banco")(0).Text
    coluna = coluna + 1: Worksheets(response).Cells(linha, coluna) = item.SelectNodes("//bancoAuto")(0).Text

    linha = linha + 1
    i = i + 1

Next item

Set xmldoc = Nothing

The result of this is a result line repeating itself multiple times. What should appear would be all the data of a itens (that would be a day) online:

data                   dataHora               rep    tipo              justificativa    ...
2018-07-09T00:00:00Z | 2018-07-09T09:01:00Z |      | MarcacaoGeoMobi |                | ...
    
asked by anonymous 17.07.2018 / 23:36

0 answers