Iterate XML with VBA and pass data to cells

Asked

Viewed 275 times

3

I’m starting to mess with VBA now 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 data of a person’s electronic dot marking. So far the code works smoothly.

What I’m struggling with is how to pass XML values to spreadsheet cells in Excel.

Example of web service 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 line of results repeating several times. What should appear would be all the data of a itens (that would one day) online:

data                   dataHora               rep    tipo              justificativa    ...
2018-07-09T00:00:00Z | 2018-07-09T09:01:00Z |      | MarcacaoGeoMobi |                | ...
xml excel vba excel-vba
  • 2

    See this example: https://stackoverflow.com/questions/31420211/reading-xml-and-repeating-a-value-across-a-cell-range I think you can use this...

  • 1

    @Evis sorry for the delay, but this link helped a lot. Thank you :)

1 answer

0

Browser other questions tagged xml excel vba excel-vba

You are not signed in. Login or sign up in order to post.