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

<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. — Tim Williams
– Tim Williams, Commented May 12, 2017 at 23:16
Adjusted both the vba and the xml file and it still doesn't work. :( — Dan
– Dan, Commented May 12, 2017 at 23:31
that's not valid XML. What is ..... ? Might be important if it includes a global namespace. — Slai
– Slai, Commented May 12, 2017 at 23:35

Tim Williams · Accepted Answer · 2017-05-12 23:35:50Z

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, 2017 at 23:35

answered May 12, 2017 at 23:25

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

6 Comments

Dan Over a year ago

I will try this and let you know. Thank you.

Dan Over a year ago

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....

Tim Williams Over a year ago

Worked for me, so maybe the XML you posted has some differences from the one you're actually using?

Dan Over a year ago

I copy and pasted it. :/

Dan Over a year ago

It wont let me upvote it because i dont have enoivh upvotes mysef

|

gembird · Accepted Answer · 2017-05-13 17:55:20Z

0

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>

edited May 13, 2017 at 17:55

answered May 13, 2017 at 9:32

gembird

14.1k5 gold badges39 silver badges54 bronze badges

7 Comments

Dan Over a year ago

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 Over a year ago

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 Over a year ago

I know in other languages its, "Trim"

gembird Over a year ago

In VBA you have Trim as well. Check Strings.Trim, Strings.LTrim, strings.RTrim.

Dan Over a year ago

Ill give it a try when i get home. Thanks for the help everyone!

|

Collectives™ on Stack Overflow

Removing Node/Attribute using VBA

2 Answers 2

6 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related