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.

I seem to remember getting an error like that when the xml document was not well formed. Does it have the xml element at the very beginning? — Mansfield
– Mansfield, Commented Nov 21, 2013 at 19:05
i started the code in my orig post at line 2. this is the first line: <?xml version="1.0" encoding="iso-8859-1" ?><?xml-stylesheet type="text/xsl" href="C:\Data\horizontal.xsl"?> — mkodikan
– mkodikan, Commented Nov 21, 2013 at 20:22

Siddharth Rout · Accepted Answer · 2013-11-21 19:10:01Z

2

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

enter image description here

answered Nov 21, 2013 at 19:10

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse XML with VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related