Iterating XML nodes using VBA

Question

Note:- It just might be a iterating XML nodes using VBA question. Please look at the bottom of this question. It would be good If we can iterate without using MSXML2.DOMDocument

I see the this question which answers part of my question on how to retrieve the CustomXMLPart. However, I am not able to iterate through the Xml. That way, this might not be specific to CustomXmlPart, It just might be a iterating XML using VBA question. Following is the XML I have in my CustomXMLPart.

<Items>
<Item1>Item1</Item1>
<Item2>Item2</Item2>
<Item3>Item3</Item3>
</Items>

This is how I add the above XML as CustomXmlPart:-

static void AddCustomTableXmlPart(WordprocessingDocument document)
        {
            MainDocumentPart mainDocumentPart = document.MainDocumentPart;
            XDocument itemXml = GetItemsAsCustomXML();

            if (mainDocumentPart.GetPartsCountOfType<CustomXmlPart>() > 0)
                mainDocumentPart.DeleteParts<CustomXmlPart>(mainDocumentPart.CustomXmlParts);

            //Add a new customXML part and then add content
            var customXmlPart = mainDocumentPart.AddCustomXmlPart(CustomXmlPartType.CustomXml);

            //copy the XML into the new part...
            using (var ts = new StreamWriter(customXmlPart.GetStream()))
            {
                ts.Write(itemXml.ToString());
                ts.Flush();
            }
        }

and this is how I am accessing it in the macro:-

Dim itemNode As xmlNode
Dim itemChildren As XMLNodes

' The below line throws a run-time error 'Run-time error '13' - 'type mismatch ' not sure why.

**Set itemChildren= ActiveDocument.CustomXMLParts(ActiveDocument.CustomXMLParts.Count).SelectSingleNode("//Items").ChildNodes**

Interestingly, when I quick watch ActiveDocument.CustomXMLParts(ActiveDocument.CustomXMLParts.Count).SelectSingleNode("//Items").ChildNodes, I see child items in the quick watch window. Is the assignment to the itemChildren variable incorrect?

I want to iterate through all the items and get get text for all of them. Could anybody help?

Try: ActiveDocument.CustomXMLParts(ActiveDocument.CustomXMLParts.Count).SelectNodes("//Items") — CaBieberach
– CaBieberach, Commented Dec 30, 2010 at 21:08
Remou, the part I am getting error is VBA. I am adding the CustomXMLPart using C#. — Ashish Gupta
– Ashish Gupta, Commented Dec 31, 2010 at 1:36
CaBieberach, ActiveDocument.CustomXMLParts(ActiveDocument.CustomXMLParts.Count).SelectNodes("//Items").Count givee me only 1 item i.e. root node. — Ashish Gupta
– Ashish Gupta, Commented Dec 31, 2010 at 1:42

Ashish Gupta · Accepted Answer · 2011-01-04 09:16:04Z

2

Ok, this is how I did it. Posting just in case it is useful for somebody. Apparently, you need not use "CustomXMLNodes" object. This can still be done using SelectNodes. The below function shows that. Also, I was not checking for empty string while adding item to the list.

Sub LoadItems()
     Dim totalItemsCount As Integer
     totalItemsCount = ActiveDocument.CustomXMLParts(ActiveDocument.CustomXMLParts.Count).SelectNodes("//Items")(1).ChildNodes.Count
     Dim item As String

     For i = 1 To totalItemsCount
        item = ActiveDocument.CustomXMLParts(ActiveDocument.CustomXMLParts.Count).SelectNodes("//Items")(1).ChildNodes(i).text
        item = Replace(item, " ", Empty)

        If Len(item) > 1 Then
        ItemUserControl.lstItems.AddItem pvargItem:item
        End If
     Next i
End Sub

answered Jan 4, 2011 at 9:16

Ashish Gupta

15.2k21 gold badges81 silver badges136 bronze badges

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

Comments

CaBieberach · Accepted Answer · 2011-01-03 00:50:57Z

0

Use:

Dim itemChildren As CustomXMLNodes

answered Jan 3, 2011 at 0:50

CaBieberach

1,7682 gold badges17 silver badges26 bronze badges

Comments

Nick Spreitzer · Accepted Answer · 2011-01-03 01:28:03Z

0

I think the reason you're getting a type mismatch error is because ChildNodesreturns a IXMLDOMNodeList not XMLNodes.

Try Dim itemChildren As IXMLDOMNodeList instead.

http://msdn.microsoft.com/en-us/library/ms757053(v=vs.85).aspx

answered Jan 3, 2011 at 1:28

Nick Spreitzer

10.6k5 gold badges41 silver badges59 bronze badges

Collectives™ on Stack Overflow

Iterating XML nodes using VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related