CODEWITHSUNDEEP
excelxmlvbaxpath
H0ttenburg
H0ttenburg

Reputation: 53

XPath evaluation in VBA (Excel)

I have an xml file to be evaluated in Excel 2010. The xml file looks as follows:

<Account>
    <Entry>
        <Value>5</Value>
    </Entry>
    <Entry>
        <Value>4</Value>
    </Entry>
    <Entry>
        <Value>-3.6</Value>
    </Entry>
</Account>

I want to sum over all values of every 'Entry' that fits some specified conditions. The evaluation which I need looks as follows:

sum(/*/Entry[Date[starts-with(., '04') and contains(., '2014')]][Value < 0.0][not(ContraEntryID)]/Value)

I don't know how to get this evaluated in Excel. What I get so far is a selection where I sum up over every item of it afterwards but there must be a better way to directly get the evaluation right? This is what I have already written:

Private Sub getSumOfValues()

Dim xmlFile As String
xmlFile = "..."

Dim xmlDoc As New MSXML2.DOMDocument60
Dim xmlSelection As IXMLDOMSelection

xmlDoc.async = False
xmlDoc.validateOnParse = True

xmlDoc.Load (xmlFile)

xmlDoc.setProperty "SelectionLanguage", "XPath"

Set xmlSelection = xmlDoc.SelectNodes("/*/Entry[Date[starts-with(., '04') and contains(., '2014')]][Value < 0.0][not(ContraEntryID)]/Value")

Dim i As Integer
Dim sum As Double
sum = 0
Dim val As String

For i = 0 To xmlSelection.Length - 1
    val = xmlSelection.Item(i).Text
    val = Replace(val, ".", ",")
    sum = sum + CDbl(val)
Next i
Debug.print(sum)   
End Sub

Upvotes: 4

Views: 6283

Answers (2)

T.M.
T.M.

Reputation: 9938

Yes, there's a direct evaluation possible now via FilterXML

Using the newer FilterXML function it's possible since 2013/16

  1. to evaluate an XPath expression directly to an array and
  2. in a 2nd step to sum the resulting contents:

Example call

I added a further condition to sum only values greater 0; can be changed to your needs.

Sub EvalIt()
' Purp: Sum up over every <Value> node contents in a given xml file
' Help: https://learn.microsoft.com/de-de/office/vba/api/excel.worksheetfunction.filterxml

'a) get xml path
    Dim xmlfile As String
    xmlfile = "C:\Users\Admin\Documents\Stack\xml\test.xml"  ' << change to your needs
'b) define XPath (search string in XML including example condition > 0)
    Dim XPath As String
    XPath = "//Value[.>0]"
'c) show result via function getSum() ~> items greater zero: (5+4)=9
    Debug.Print "Sum of <Value> node contents greater 0 = " & _
                getSum(xmlfile, XPath)

End Sub

Function getSum()

Function getSum(ByVal xmlfile As String, ByVal XPath As String) As Double
'   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'   Assign xml values to 1-based 2-dim array via function FilterXML
'   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Dim arr As Variant
    arr = WorksheetFunction.FilterXML(getXMLContent(xmlfile), XPath)

''   (optional) reduction to "flat" 1-dim array
'    arr = Application.Transpose(Application.Index(arr, 0, 1))
''   (optional) display of single array values in VBE Immediate Window
'    Debug.Print Join(arr, "|")

'   return result
    getSum = Application.Sum(arr)

End Function

Help function getXMLContent() called by getSum()

This function gets the (well formed) xml content as string. It distinguishes between - http:// Get calls using the WebService() function or - a local file path needing a direct load via MSXML (early bound in this example).

Function getXMLContent(ByVal xmlfile As String)
If Left$(xmlfile, 4) = "http" Then           ' a) URL
    getXMLContent = WorksheetFunction.WebService(xmlfile)
Else                                         ' b) a local file
    Dim xmlDoc As New MSXML2.DOMDocument60   ' early bound via Microsoft XML v6.0
    xmlDoc.Load xmlfile                      ' load xmlFile
    getXMLContent = xmlDoc.XML               ' return entire xml content string
End If
End Function

Caveat

I didn't include error handling for invalid file pathes or XPath constructions in order to allow a short, but clear view.

Upvotes: 1

Martin Honnen
Martin Honnen

Reputation: 167446

As far as I know, Msxml only supports evaluating XPath expressions that return a node set, there is no API to evaluate an XPath expression that returns a primitive value like a number. With MSXML, all you could is create and execute a stylesheet, that does an <xsl:value-of select="sum(...)"/>.

There are other XPath implementations like in the Microsoft .NET framework, that offer an Evaluate method. Whether you can use that from Office I am not sure.

Upvotes: 4

Related Questions