CODEWITHSUNDEEP
xmlvbaappendchild
pavan kumar
pavan kumar

Reputation: 25

cant append child nodes to a parent node( VBA-XML)

Here is my VBA code where I am modifying an XML with excel datasheet. xml is here : https://www.jiocloud.com/s/?t=SzqFJhEABfsTQfZW&s=a2

function fnUpdateXMLByTags()
Dim mainWorkBook As Workbook
Dim wrsht As Worksheet


Set mainWorkBook = ActiveWorkbook
Set wrsht = mainWorkBook.Sheets("Sheet1")
wrsht.Activate

Dim oXMLFile As MSXML2.DOMDocument60
Set oXMLFile = New MSXML2.DOMDocument60
oXMLFile.async = False
oXMLFile.validateOnParse = False
XMLFileName = "Z:\IPC\IPC1752A_WK-200264-000 - Copy (2).xml"
XmlNamespaces = "xmlns:d='http://webstds.ipc.org/175x/2.0'"
oXMLFile.SetProperty "SelectionNamespaces", XmlNamespaces

For i = 3 To 5
  If Not IsEmpty(mainWorkBook.Sheets("Sheet1").Range("A" & i)) Then

        PartID = mainWorkBook.Sheets("Sheet1").Range("A" & i).Value
        PartName = mainWorkBook.Sheets("Sheet1").Range("B" & i).Value
        MaterialName = mainWorkBook.Sheets("Sheet1").Range("D" & i).Value
        MassAmount = mainWorkBook.Sheets("Sheet1").Range("F" & i).Value
        MassUnit = mainWorkBook.Sheets("Sheet1").Range("G" & i).Value
        Path = "D:\New folder\" & PartID & ".xml"

        If oXMLFile.Load(XMLFileName) Then

            Set PartIDNodes = oXMLFile.SelectNodes("//@itemNumber")
            Set PartNameNodes = oXMLFile.SelectNodes("//@itemName")
            Set MaterialNameNodes = oXMLFile.SelectNodes("//@name")
            Set MassAmountNodes = oXMLFile.SelectNodes("//@value")
            Set MassUnitNodes = oXMLFile.SelectNodes("//@UOM")
            PartIDNodes(0).NodeValue = Part_ID
            PartNameNodes(0).NodeValue = PartName
            MaterialNameNodes(5).NodeValue = MaterialName
            MassAmountNodes(1).NodeValue = MassAmount
            MassUnitNodes(1).NodeValue = MassUnit

        End If

    Else:
        If IsEmpty(mainWorkBook.Sheets("Sheet1").Range("D" & i)) Then
            Substancename = mainWorkBook.Sheets("Sheet1").Range("H" & i).Value
            CASNumber = mainWorkBook.Sheets("Sheet1").Range("I" & i).Value
            SubAmount = mainWorkBook.Sheets("Sheet1").Range("J" & i).Value
           Set SubstanceCategoryNode = oXMLFile.SelectNodes("//d:SubstanceCategory")
           Set Substancenode = oXMLFile.createElement("d:Substance")
           Substancenode.Text = "<SubstanceID identity="" authority=""/> <Amount value="" UOM=""/> "
           SubstanceCategoryNode.appendChild (Substancenode)
             "throwwin error here"

           oXMLFile.Save Path
         End If


End If


Next i

End Function

when I append a child to parent it throws "Object doesn't support this property or method"

please suggest where I am wrong.is there is any other way to append a child node to a parent node

Upvotes: 0

Views: 2577

Answers (3)

pavan kumar
pavan kumar

Reputation: 25

The answer is simple. Instead of 

Set SubstanceCategoryNode = oXMLFile.SelectNodes("//d:SubstanceCategory")

I have to use this.

Set SubstanceCategoryNode = oXMLFile.SelectsingleNode("//d:SubstanceCategory")

from SelectNodes to selectsinglenode.

Thanks to both @qharr and @ThomasMX for support.

Upvotes: 1

QHarr
QHarr

Reputation: 84465

Just for fun. The original source had four "Substance" nodes sitting under substance category.

Nodes

Before:

Before

You can append a child node i.e. adding another Substance node (I am guessing?) and an easy way is to clone an existing and then change its properties e.g. 

Option Explicit

Public Sub test()
    Dim xml As String, doc As MSXML2.DOMDocument60
    xml = [A1].Text
    Set doc = New MSXML2.DOMDocument60

    If Not doc.LoadXML(xml) Then
        Err.Raise doc.parseError.ErrorCode, , doc.parseError.reason
        Exit Sub
    End If

    Dim node As IXMLDOMElement, newNode As IXMLDOMElement
    Set node = doc.DocumentElement.LastChild.ChildNodes(1).FirstChild.FirstChild.ChildNodes(1).ChildNodes(1) '<== MaterialInfo > HomogeneousMaterialList>HomogeneousMaterial>SubstanceCategoryList > SubstanceCategoryListID>SubstanceCategory

   Set newNode = node.appendChild(node.LastChild.CloneNode(True))

   'Adjust newNode properties
    Stop
End Sub

After:

Cloned node appended

Upvotes: 1

ThomasMX
ThomasMX

Reputation: 1822

The reason is because the type of SubstanceCategoryNode is IXMLDOMSelection. It is a selection, which is just a view of the XML tree. To build/append elements, I'd actually create an element, like the Substancenode variable above... 

Set SubstanceCategoryNode = oXMLFile.createElement("d:SubstanceCategory")

... or so

Use the watch window/TypeName() function

Upvotes: 2

Related Questions