Create numbered XML nodes set attributes when creating XML node

0 votes

I have a macro in Excel that reads XML information from one file and duplicates them in another. Since the second file has more and different information and user input determines what is being mapped, I am unable to simply copy files. The user chooses which genres to copy from source to target, for instance, using the traditional example of a movie database. I have a spreadsheet with the XPaths for the source and target, and I need to map particular variables across. Not all of the tags are present in the new XML file, therefore before their values can be entered, they must be produced by my code.

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?

Feb 20, 2023 in Others by narikkadan
• 63,420 points
649 views

1 answer to this question.

0 votes

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
answered Feb 20, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How do I start studying Node.JS to create a restful API?

Node.js is an open source server environment ...READ MORE

answered Jun 11, 2019 in Others by ArchanaNagur
• 2,360 points
738 views
0 votes
1 answer

Why and when to use Node.js?

The major reason why they are going ...READ MORE

answered Jun 14, 2019 in Others by sunshine
• 1,300 points

edited Jun 14, 2019 by sunshine 739 views
0 votes
1 answer

web.xml is missing and <failOnMissingWebXml> is set to true

You can also use the following method: Right ...READ MORE

answered Feb 18, 2022 in Others by Aditya
• 7,680 points
795 views
0 votes
1 answer

Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
810 views
0 votes
1 answer
0 votes
1 answer

Convert image (jpg) to base64 in Excel VBA?

Heres a function. Can't remember where I ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,420 points
2,049 views
0 votes
1 answer

Sending excel data to Tally

You can use Requests to send the ...READ MORE

answered Nov 19, 2022 in Others by narikkadan
• 63,420 points
586 views
0 votes
1 answer

How can I preserve the format while exporting data from excel to evernote

The contents for an Evernote note are ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,420 points
319 views
0 votes
1 answer

Type mismatch error when referring to array element by location vba

We utilise MID to parse the string. ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
519 views
0 votes
1 answer

Excel VBA creating a new Outlook appointment results in a cancelled appointment

Because an inappropriate sender will be used, ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
584 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP