Removing Node/Attribute using VBA

Question

I've tried the searches of this site and a google search and can't seem to find an answer to my problem although the searches I did conduct led me to the VBA code you see below. My problem is that I have an XML tree and I need to remove the 8 lines associated with "MyBank1","broccoli" etc and "MyBank2", "broccoli" etc. The files I need to edit could have many instances of "foo" in the file where the "MyBank1" and "MyBank2" lines need to be removed (thus the loop) but leave the rest of the bank information (for example leave the lines associated with "Citi"). The code runs but the resulting file doesn't remove the lines. I have a feeling I'm not understanding XML language, the interaction of VBA with the xml file or more likely, both. Any help is appreciated!

my xml file:

<?xml version="1.0" encoding="utf-8"?>
<IPSGDatas.....xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <header>
        <language_id>120</language_id>
    </header>
    <datas>
        <foo>
            <signature/>
            <bar>
                <banks>
                    <marker>
                        <broccoli order="1">X</broccoli>
                        <broccoli order="2">X</broccoli>
                    </marker>
                    <bank name="Citi">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="Keybank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NBT">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NationalBank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="MyBank1">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="MyBank2">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                </banks>
                <profile_id>MyName1</profile_id>
            </bar>
            <action_id>New</action_id>
            <index_id>1</index_id>
            <agency/>
            <agency_reference/>
            <accreditation_id>U</accreditation_id>
        </foo>
        <foo>
            <signature/>
            <bar>
                <banks>
                    <marker>
                        <broccoli order="1">X</broccoli>
                        <broccoli order="2">X</broccoli>
                    </marker>
                    <bank name="Citi">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="Keybank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NBT">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NationalBank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="MyBank1">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="MyBank2">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                </banks>
                <profile_id>MyName1</profile_id>
            </bar>
            <action_id>New</action_id>
            <index_id>1</index_id>
            <agency/>
            <agency_reference/>
            <accreditation_id>U</accreditation_id>
        </foo>
    </datas>
</IPSGDatas>

Now my VBA Code:

Option Explicit
Public Sub EditDocument()

'declare objects and variables
Dim xDoc As MSXML2.DOMDocument60
Dim xNode As IXMLDOMElement
Dim foo As IXMLDOMNodeList
Dim i As Integer

'initialize object
Set xDoc = New MSXML2.DOMDocument60
xDoc.validateOnParse = False
'load document
xDoc.Load ("C:\Users\Danny\Desktop\xml\TestDoc.xml")

'initialize and select set of nodes
Set foo = xDoc.SelectNodes("/datas/foo")

    'loop to select specific attribute/node and delete it
    For i = 0 To foo.Length - 1
        Set xNode = xDoc.SelectSingleNode("/bar/banks/bank[@name='MyBank1']")
        xNode.Attributes.removeNamedItem "MyBank1"
        Set xNode = xDoc.SelectSingleNode("/bar/banks/bank[@name='MyBank2']")
        xNode.Attributes.removeNamedItem "MyBank2"
    Next i

'save new document
xDoc.Save ("C:\Users\Danny\Desktop\xml\NewFile.xml")
'clear document from memory
Set xDoc = Nothing

End Sub

`` is an empty element with no content, but your indenting makes it look like it's part of the path to `bank`: it is not. — Tim Williams, May 12 '17 at 23:16
Adjusted both the vba and the xml file and it still doesn't work. :( — Dan, May 12 '17 at 23:31
that's not valid XML. What is `.....` ? Might be important if it includes a global namespace. — Slai, May 12 '17 at 23:35
No it isnt valid xml. It has nothing to do with the problem and I left it out purposely. It contains information about what the data is specifically (which ive also changed) just to keep everything a littlw more secure. I apologize for the confusion it caused. I should have mentioned it. — Dan, May 13 '17 at 00:09
@Slai has it. If I remove what is in the namespace, it works. The question is now, I'm not quite sure how to handle this node itself. It's automatically there downloaded from the program generated from and needs to be included in the program uploaded to. any ideas? — Dan, May 13 '17 at 15:15
http://stackoverflow.com/questions/27563616/how-to-ignore-a-xml-namespace — Slai, May 13 '17 at 15:31

Tim Williams · Accepted Answer · 2017-05-12T23:35:50.677

3

<signature/> is an empty element with no content, but your indenting makes it look like it's part of the path to <bank>: it is not so leave it out of the xPath.

Eg:

Public Sub EditDocument()


    Dim xDoc As MSXML2.DOMDocument60

    Set xDoc = New MSXML2.DOMDocument60
    xDoc.validateOnParse = False
    xDoc.Load "C:\_Stuff\test\test.xml" 

    DeleteNodes xDoc, "IPSGDatas/datas/foo/bar/banks/bank[@name='MyBank1']"
    DeleteNodes xDoc, "IPSGDatas/datas/foo/bar/banks/bank[@name='MyBank2']"

    xDoc.Save "C:\_Stuff\test\test_updt.xml"  ''save new document
    Set xDoc = Nothing

End Sub

Sub DeleteNodes(xDoc As MSXML2.DOMDocument60, xPath As String)
    Dim foo As IXMLDOMNodeList, el  As IXMLDOMElement
    Set foo = xDoc.SelectNodes(xPath)
    Debug.Print foo.Length & " nodes for " & xPath
    For Each el In foo
        el.ParentNode.RemoveChild el
    Next el
End Sub

edited May 12 '17 at 23:35

answered May 12 '17 at 23:25

Tim Williams

154,628
8
97
125

I will try this and let you know. Thank you. – Dan May 13 '17 at 00:11
Yep. That doesn't work either. :( I had something similar to this earlier in the day and it wouldn't go. The set of nodes for MyBank1 and MyBank2 remain in the file.... – Dan May 13 '17 at 02:51
Worked for me, so maybe the XML you posted has some differences from the one you're actually using? – Tim Williams May 13 '17 at 03:54
I copy and pasted it. :/ – Dan May 13 '17 at 09:29
It wont let me upvote it because i dont have enoivh upvotes mysef – Dan May 13 '17 at 09:44
@TimWilliams, I inserted your code to Debug prior to the DeleteNodes sub (I don't know why I didn't think of doing this to try to figure it out) and it counts 0 for nodes in xPath. It should have a node count of 8 for both of them? – Dan May 13 '17 at 14:25

Daniel Dušek · Answer 2 · 2017-05-13T17:55:20.410

Could be something like this, HTH.

Set xDoc = New MSXML2.DOMDocument60
xDoc.Load ("C:\Temp\StackOverflow\source.XML")

Dim myBank12 As IXMLDOMNodeList
Set myBank12 = xDoc.SelectNodes("//bank[@name='MyBank1']|//bank[@name='MyBank2']")

Dim xNode As IXMLDOMElement
For Each xNode In myBank12
    xNode.ParentNode.RemoveChild xNode
Next

xDoc.Save "C:\Temp\StackOverflow\result.XML"

Note:

//bank selects all bank elements no matter where they are in the document. And by using the | operator in an xpath expression you can select several paths.

Result:

<?xml version="1.0" encoding="utf-8"?>
<IPSGDatas xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <header>
        <language_id>120</language_id>
    </header>
    <datas>
        <foo>
            <signature/>
            <bar>
                <banks>
                    <marker>
                        <broccoli order="1">X</broccoli>
                        <broccoli order="2">X</broccoli>
                    </marker>
                    <bank name="Citi">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="Keybank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NBT">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NationalBank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                </banks>
                <profile_id>MyName1</profile_id>
            </bar>
            <action_id>New</action_id>
            <index_id>1</index_id>
            <agency/>
            <agency_reference/>
            <accreditation_id>U</accreditation_id>
        </foo>
        <foo>
            <signature/>
            <bar>
                <banks>
                    <marker>
                        <broccoli order="1">X</broccoli>
                        <broccoli order="2">X</broccoli>
                    </marker>
                    <bank name="Citi">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="Keybank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NBT">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NationalBank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                </banks>
                <profile_id>MyName1</profile_id>
            </bar>
            <action_id>New</action_id>
            <index_id>1</index_id>
            <agency/>
            <agency_reference/>
            <accreditation_id>U</accreditation_id>
        </foo>
    </datas>
</IPSGDatas>

Ill try that initialization dee. Thanks. I like the double \. Kind of prevents me from making a mistake in the XPath. I didnt know that the pipebar was a way to initialize both nodes. Thats kind of cool. — Dan, May 13 '17 at 09:36
What about white space? Could I have a white space character throqing everything off? Is the a quick way with vba to remove them all? — Dan, May 13 '17 at 09:41
In VBA you have Trim as well. Check Strings.Trim, Strings.LTrim, strings.RTrim. — Daniel Dušek, May 13 '17 at 09:44
Ill give it a try when i get home. Thanks for the help everyone! — Dan, May 13 '17 at 09:45
I tried that dee...it didn't work. Ill work on trimming it to see if it works. — Dan, May 13 '17 at 14:08
I have posted result, which I got when processing your input xml. As you can see the nodes `bank` which had attribute 'name =MyBank1/2' were removed. Is that what you expect? Or specify what you mean with `it didn't work`. — Daniel Dušek, May 13 '17 at 18:02

Removing Node/Attribute using VBA

2 Answers2