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 | | ...