Reputation: 1
I'm trying to parse xml with VBA to obtain test results from an electrical test system. This is an example of what the system is giving me:
<Reports>
<Report Type='UUT' Title='UUT Report' Link='-1-2013-11-20-10-2-46-867' UUTResult='Failed' StepCount='132'>
<Prop Name='UUT' Type='Obj' TypeName='UUT' Flags='0x0'>
<Prop Name='SerialNumber' Type='String' Flags='0x0'>
<Value>02</Value>
</Prop>
<Prop Name='PartNumber' Type='String' Flags='0x0'>
<Value>1009433</Value>
</Prop>
<Prop Name='LotNumber' Type='String' Flags='0x0'>
<Value>1234567</Value>
</Prop>
<Prop Name='CriticalFailureStack' Type='Array' LBound='[0]' HBound='[1]' ElementType='Obj' Flags='0x0'>
<ArrayElementPrototype Type='Obj' TypeName='NI_CriticalFailureStackEntry' Flags='0x0'>
<Prop Name='StepName' Type='String' Flags='0x0'>
<Value></Value>
</Prop>
<Prop Name='SequenceName' Type='String' Flags='0x0'>
<Value></Value>
</Prop>
<Prop Name='SequenceFileName' Type='String' Flags='0x0'>
<Value></Value>
</Prop>
<Prop Name='ResultId' Type='Number' Flags='0x0'>
<Value>0</Value>
</Prop>
</ArrayElementPrototype>
<Value ID='[0]'>
<Prop Type='Obj' TypeName='NI_CriticalFailureStackEntry' Flags='0x0'>
<Prop Name='StepName' Type='String' Flags='0x0'>
<Value>Brady Detection</Value>
</Prop>
<Prop Name='SequenceName' Type='String' Flags='0x0'>
<Value>MainSequence</Value>
</Prop>
<Prop Name='SequenceFileName' Type='String' Flags='0x0'>
<Value>1009450 AT ILR Final-test_app.seq</Value>
</Prop>
<Prop Name='ResultId' Type='Number' Flags='0x0'>
<Value>44</Value>
</Prop>
</Prop>
</Value>
<Value ID='[1]'>
<Prop Type='Obj' TypeName='NI_CriticalFailureStackEntry' Flags='0x0'>
<Prop Name='StepName' Type='String' Flags='0x0'>
<Value>Number of Brady Beats</Value>
</Prop>
<Prop Name='SequenceName' Type='String' Flags='0x0'>
<Value>Brady Detection</Value>
</Prop>
<Prop Name='SequenceFileName' Type='String' Flags='0x0'>
<Value>1009450 AT ILR Final-test_app.seq</Value>
</Prop>
<Prop Name='ResultId' Type='Number' Flags='0x0'>
<Value>49</Value>
</Prop>
I'd like to pull out the Serial Number from <prop name = 'serial number'>
, the test result from <Report UUTResult = 'Failed'>
, and the device failures from <Prop Type='Obj' TypeName='NI_CriticalFailureStackEntry' Flags='0x0'> <Value>Brady Detection</Value>
I've never worked with XML before, but this is what I've gotten by reading this site so far:
Dim xml As New MSXML.DOMDocument60
Dim xReport As IXMLDOMElement
Dim result As String
xml.Load (filePath)
Set xReport = xml.SelectSingleNode("//Reports/Report/")
result = xReport.getAttribute("UUTResult")
When I run this, I get "Run-time error '-2147467259 (80004005)': Automation Error Unspecified Error at Set xReport.
I also tried casting xml as new MSXML2.DOMDocument (rather than DOMDocument60) and then I get a type mismatch at the same line.
Any help is much appreciated!
Thanks.
Upvotes: 0
Views: 1144
Reputation: 149325
Here is one way to get your data without using MSXML
TRIED AND TESTED
If your format of the xml doesn't change than this will give you what you want.
Option Explicit
Sub Sample()
Dim MyData As String, strData() As String
Dim i As Long
Dim sString As String
'~~> Replace your file here
Open "C:\Sample.xml" For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)
For i = LBound(strData) To UBound(strData)
If InStr(1, strData(i), "<Prop Name='SerialNumber'", vbTextCompare) Then
sString = strData(i + 1)
Exit For
End If
Next
If sString <> "" Then
Debug.Print Trim(Replace(Replace(sString, "<Value>", ""), "</Value>", ""))
End If
End Sub
Output
Upvotes: 2