4

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
Improve this question
1
  • Added a solution to a direct XPath evaluation via the new FilterXML function in Excel 2013/16/19 Commented Feb 17, 2020 at 21:31

2 Answers 2

Reset to default
4

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.

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

2 Comments

The hint with creating a stylesheet did it! I found a source (unfortunately it is in German) where it was done like that: xing.com/net/vbatreff/vba-fragen-und-antworten-q-a-654605/… And you were right! There is no evaluation directly in VBA with Excel possible! Thank you very much!
@Martin-Honnen - posted a solution to a direct XPath evaluation via the new FilterXML function +:)
1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.