XML data split in excel, or use macro?

[chomp99]

Hello,

I need help with splitting up data in XML blocks. I have an excel spreadsheet: 1st column contains number values (ids) and the 2nd column contains XML code snippets (like paragraphs that contains sets of data)

I want to have a 3rd, 4th, 5th column display the data from the categories inside the XML code.

Is there a macro that can look in the cell containing the XML and takes out the values and puts them in other cells?

Please help, I cant seem to make this work out of a spreadsheet. I tried linking excel to access and opening .xml text files in excel and cant seem to break up the XML all at once.

thanks!

 

[jel]

I do think that excel is not really the right format to store xml: xml is.
Anyway, you could add a function that takes 2 arguments: a reference to a cell, and an xpath-expression.
This what you'd have to do:
add a module in your VB-editor.
Set a reference to msxml (I used msxml4, but msxmml3 will do also). You'll find references under 'extra' in the vb-editor.
paste this code:

Option Explicit

Public Function NodeValue(ByVal CellReference As Range, ByVal Xpath As String) As String

    Dim objDom As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMNode
    Dim strResult As String

    Set objDom = New MSXML2.DOMDocument40
    objDom.setProperty "SelectionLanguage", "XPath"
    If objDom.loadXML(CellReference.Text) Then
        Set objNode = objDom.selectSingleNode(Xpath)
        If objNode Is Nothing Then
            strResult = "no node found"
        Else
            strResult = objNode.Text
        End If
    Else
        strResult = "no valid xml"
    End If
    
    NodeValue = strResult

End Function

In the excel-sheet you now can enter this formula in any cell:
=NodeValue(A1,"//mynode"), wich will print the node-value of the first "mynode" from the xml-text that is in cell A1.

enjoy