Iterate XML with VBA and pass data to cells

Question:

I'm starting to work now with VBA and I have the following problem: I managed to make a code that makes a SOAP request to a webservice and returns an XML with the data from a person's time and attendance. So far the code works fine.

What I'm having trouble with is how to pass XML values ​​to spreadsheet cells in Excel.

Sample webservice response: https://gist.github.com/rafinhacarneiro/084402c59a2f1a0e0e957baf7e6f27d9

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 row of results repeating over and over again. What should appear would be all the data for an itens (which would be one day) in line:

data                   dataHora               rep    tipo              justificativa    ...
2018-07-09T00:00:00Z | 2018-07-09T09:01:00Z |      | MarcacaoGeoMobi |                | ...

Answer:

Good morning, For your case, I suggest using the powerquery editor, because you will be able to manipulate the xml and convert it into a spreadsheet more easily.

Att,

link to download powerquery https://support.microsoft.com/en-us/office/iniciar-o-suplemento-power-pivot-para-o-excel-a891a66d-36e3-43fc-81e8-fc4798f39e8

Scroll to Top