Getting Specific Data Through Parent Node-CodePudding

I have XML File which contains different data. In this file under the root node (PulserDeviceData), there is one Parent Node (IdentityNumber), IdentityNumber contains one child named (Measurement). Measurement contains 4 sub chile PulserData. PulserData holds the values for IdentityNumber. Pulser data is divided into two groups theoretically i.e., 2 sub children contain the data with the same value of two variables i.e., ( PRF = 1000 & Volt = 50). The next 2 sub children values are coming with (PRF = 5000 & Volt = 50).

Now I want to categorize values on these two variables as fixed values. For example, when values with ( PRF = 1000 & Volt = 50) came I want to populate excel from Row number 50 ..... 100 and when values with setting (PRF = 5000 & Volt = 50) came i want to populate from 101 .... 150

This is how I am reading the data currently

Set PulsTest = XDoc.SelectNodes("//TestDevice/PulsTestData/PulserDeviceData")
Public sngPulserVolt(0 To 15, 0 To 3) As Single
Public  sngPulserFall(0 To 15, 0 To 3) As Single
Public sngPulserWidth(0 To 15, 0 To 3) As Single
        For i = o To Data.Length - 1
        For j = 0 To Data(i).ChildNodes.Length - 1
          
            If Data(i).ChildNodes(j).BaseName = "IdentityNumber" Then
                intIdentityNumber = Data(i).ChildNodes(j).nodetypedvalue
            End If
        
        Next
   
        For j = 0 To Data(i).ChildNodes(1).ChildNodes.Length - 1
            sngPulserVolt(intIdentityNumber - 1, j) = CSng(ConDecimalSep(Data(i).ChildNodes(1).ChildNodes(j).ChildNodes(0).nodetypedvalue))
            sngPulserFall(intIdentityNumber - 1, j) = CSng(ConDecimalSep(Data(i).ChildNodes(1).ChildNodes(j).ChildNodes(1).nodetypedvalue))
            sngPulserWidth(intIdentityNumber - 1, j) = CSng(ConDecimalSep(Data(i).ChildNodes(1).ChildNodes(j).ChildNodes(2).nodetypedvalue))
  Next

This is How XML looks like. I just posted that XML for 2 identity numbers this XML goes to 16 identity numbers with the same structure

<PulserDeviceData>
  <IdentityNumber>1</IdentityNumber>
  <Measurements>
    <PulserData>
      <PulserAmplitude>-141.25</PulserAmplitude>
      <PulserFall>18.77</PulserFall>
      <PulserWidth>46.13</PulserWidth>
      <SetWidth>40</SetWidth>
      <SetPRF>1000</SetPRF>
      <SetVoltage>50</SetVoltage>
    </PulserData>
    <PulserData>
      <PulserAmplitude>-162.7</PulserAmplitude>
      <PulserFall>23.54</PulserFall>
      <PulserWidth>74.555</PulserWidth>
      <SetWidth>60</SetWidth>
      <SetPRF>1000</SetPRF>
      <SetVoltage>50</SetVoltage>
    </PulserData>
    <PulserData>
      <PulserAmplitude>-162.75</PulserAmplitude>
      <PulserFall>23.445</PulserFall>
      <PulserWidth>107.975</PulserWidth>
      <SetWidth>80</SetWidth>
      <SetPRF>5000</SetPRF>
      <SetVoltage>50</SetVoltage>
    </PulserData>
    <PulserData>
      <PulserAmplitude>-162.75</PulserAmplitude>
      <PulserFall>23.395</PulserFall>
      <PulserWidth>135.275009</PulserWidth>
      <SetWidth>100</SetWidth>
      <SetPRF>5000</SetPRF>
      <SetVoltage>50</SetVoltage>
    </PulserData>
  </Measurements>
</PulserDeviceData>
<PulserDeviceData>
  <IdentityNumber>2</IdentityNumber>
  <Measurements>
    <PulserData>
      <PulserAmplitude>-136.85</PulserAmplitude>
      <PulserFall>17.66</PulserFall>
      <PulserWidth>45</PulserWidth>
      <SetWidth>40</SetWidth>
      <SetPRF>1000</SetPRF>
      <SetVoltage>50</SetVoltage>
    </PulserData>
    <PulserData>
      <PulserAmplitude>-163.65</PulserAmplitude>
      <PulserFall>23.745</PulserFall>
      <PulserWidth>74.55</PulserWidth>
      <SetWidth>60</SetWidth>
      <SetPRF>1000</SetPRF>
      <SetVoltage>50</SetVoltage>
    </PulserData>
    <PulserData>
      <PulserAmplitude>-163.2</PulserAmplitude>
      <PulserFall>23.695</PulserFall>
      <PulserWidth>107.23</PulserWidth>
      <SetWidth>80</SetWidth>
      <SetPRF>5000</SetPRF>
      <SetVoltage>50</SetVoltage>
    </PulserData>
    <PulserData>
      <PulserAmplitude>-163.45</PulserAmplitude>
      <PulserFall>23.665</PulserFall>
      <PulserWidth>134.715</PulserWidth>
      <SetWidth>100</SetWidth>
      <SetPRF>5000</SetPRF>
      <SetVoltage>50</SetVoltage>
    </PulserData>
  </Measurements>
</PulserDeviceData>

I want to make an array for these values (PulserAmplitude, PulserFall, PulserWidth) with two settings of PRF and Voltage. This array should store the values of three variables matching the condition of PRF and Volt values which I posted above. The required output should look like this

The identity number will go till 16 I just pasted for 2. So for all identity numbers in my excel sheet first I want to populate the data for PRF = 1000 & Volt = 50 && Next (PRF = 5000 & Volt = 50).

CodePudding user response：

First you have to declare i and j as long, then i = o (=letter) would give error put Option Explicit on top this is code without checking, to help with the idea;

For i = 0 to 15
   For j = 0 to 3
     If sngPulserFall(i,j) = 1000 and sngPulserVolt(i,j) = 50 then 
         Sheet.range(....) = sngPulserFall(i,j)
         Sheet.range(...) = sngPulserVolt(i,j)
     end if
   next
next

CodePudding user response：

Not sure if you need to fill 3 arrays, you could write the values to the sheet directly.

Option Explicit
    
Sub processXML()

    Const XMLFILE = "data.xml"

    Dim wb As Workbook, ws As Worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)
    
    Dim xml As Object, data As Object
    Dim node1 As Object, node2 As Object, node3 As Object
    Dim intIdentityNumber As Long, i As Integer
    Dim outRow As Long, outRow1 As Long, outRow2 As Long, v(3) As String
    outRow1 = 50
    outRow2 = 101
    
    ' read xml
    Set xml = CreateObject("MSXML2.DOMDocument")
    xml.Load XMLFILE
    Set data = xml.SelectNodes("//TestDevice/PulsTestData/PulserDeviceData")
    
    ' process each PulseDeviceData
    For i = 0 To data.Length - 1
        
        For Each node1 In data(i).ChildNodes
            
            If node1.BaseName = "IdentityNumber" Then
                intIdentityNumber = node1.NodeTypedValue
            ElseIf node1.BaseName = "Measurements" Then
               
               ' PulserData
               For Each node2 In node1.ChildNodes
                  
                  For Each node3 In node2.ChildNodes
                      'Debug.Print intIdentityNumber, node3.BaseName, node3.NodeTypedValue
                      Select Case node3.BaseName
                         Case "PulserAmplitude"
                             v(1) = node3.NodeTypedValue
                         Case "PulserFall"
                             v(2) = node3.NodeTypedValue
                         Case "PulserWidth"
                             v(3) = node3.NodeTypedValue
                         Case "SetPRF"
                            ' select outrow 1000 start 50, 5000 start 101
                            If node3.NodeTypedValue = 1000 Then
                                outRow = outRow1
                                outRow1 = outRow1   1
                            ElseIf node3.NodeTypedValue = 5000 Then
                                outRow = outRow2
                                outRow2 = outRow2   1
                            Else
                                MsgBox "Error SetPRF value = " & node3.NodeTypedValue, vbCritical
                                Exit Sub
                            End If
                            
                         Case "SetVoltage"
                             ' Output to sheet
                             If node3.NodeTypedValue = "50" Then
                                  ws.Cells(outRow, 1) = intIdentityNumber
                                  ws.Cells(outRow, 2) = v(1)
                                  ws.Cells(outRow, 3) = v(2)
                                  ws.Cells(outRow, 4) = v(3)
                                  'Debug.Print outRow, v(1), v(2), v(3)
                                  Erase v
                             End If
                      End Select
                  Next
               Next
            End If
                   
        Next
    Next
    MsgBox XMLFILE & " processed", vbInformation
End Sub