Create numbered XML nodes, set attributes when creating XML node

Question

I've got an excel macro which is reading XML values from one file and replicating them in a second. I can't just copy files, as the second file has more and different stuff in it, and the user input determins what is being mapped. E.g. to use the classic example of a movie database, the user picks which genres to copy from source to target. I need to maps specific values across; the XPaths of the source and target are in a spreadsheet. Not all of the tags exist in the new XML file, so they need to be created my my code before their values can be populated.

I've got a great start from Greg-R's work at Create xml file based on xPath from Excel with VBA ; but his code doesn't handle numbered nodes. I've easily stripped the number from the target tag, but can't figure out the correct methods for adding the attribute to the node. E.g. the XPath could be //Movies/Title[@number=1]/Actor[@number=5].Name

Here's what I've got so far:

Sub makeXPath(xmldoc As Object, xpath As String)
'Original code from: https://stackoverflow.com/questions/12149941/create-xml-file-based-on-xpath-from-excel-with-vba
Dim partsOfPath() As String
Dim oNodeList As IXMLDOMNodeList
Dim strXPathQuery As String
Dim sParent As String
Dim objRootElem As IXMLDOMElement
Dim objMemberElem As IXMLDOMElement
Dim objMemberName As IXMLDOMElement
Dim objParent As Object

Set objParent = xmldoc

partsOfPath = Split(xpath, "/")

For i = LBound(partsOfPath) To UBound(partsOfPath)
    If strXPathQuery > "" Then strXPathQuery = strXPathQuery & "/"
    strXPathQuery = strXPathQuery & partsOfPath(i)

    Set oNodeList = xmldoc.SelectNodes(strXPathQuery)

    If oNodeList.Length = 0 Then
        'if I don't have the node, create it
        Debug.Print "partsOfPath(" & i & ") = " & partsOfPath(i)

        NumberPos = InStr(partsOfPath(i), "[@number=")
        If NumberPos > 0 Then
            'Numbered node, extract the number
            ElementName = Left(partsOfPath(i), NumberPos - 1)
            'Len("[@number=") = 9. Speed the code up by not calculating it each time. Every little bit helps!
            NodeNumber = Mid(partsOfPath(i), NumberPos + 9, Len(partsOfPath(i)) - NumberPos - 9)
        Else
            ElementName = partsOfPath(i)
            NodeNumber = ""
        End If

        Set objMemberElem = xmldoc.createElement(ElementName)
        objParent.appendChild objMemberElem

        If Not NodeNumber = "" Then
            objMemberElem.createAttribute ("number")         '<<<------ This bit is throwing errors :(
            .createAttribute ("number")
            
            objParent.Attributes.setNamedItem(objAttr).Text = NodeNumber
        End If
        'setting the parent for the next element of the path
        Set objParent = objMemberElem
    Else
        'setting parent to first iteration, until I make adjustment otherwise later
        Set objParent = oNodeList.Item(0)
    End If

Next

End Sub

I've researched this til I'm blind (How many tabs can Chrome handle?) and tried various methods, but none are working. What are the methods I should be using?

Thanks for your help legends!

Interesting question. Please show sample XML, sample spreadsheet data, and desired XML for a [mre]. — Parfait, Feb 19 '23 at 13:09

score 1 · Accepted Answer · answered Feb 19 '23 at 01:06

Just like createElement, createAttribute is a method of the xml document, not of a node like objMemberElem.

This should work:

If Not NodeNumber = "" Then
    Set objAttr = xmldoc.createAttribute("number")  
    objAttr.Value = NodeNumber
    objMemberElem.Attributes.setNamedItem objAttr
End If

Create numbered XML nodes, set attributes when creating XML node

1 Answers1