Parse XML with VBA

Question

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.

Answer 1

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