VBA to add XML to XML file in MS Word

Hi All,

I have an XML file to which I would like to add a new node / attribute... I can get it working to a point but VBA insists on adding a NameSpace which I don't want...

Here is the XML file contents that I already have:

<?xml version="1.0" standalone="yes"?>
<my_Ribbon xmlns="http://www.drTribos/Ribbon/EngAU" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<ID ID="Unique1">
		<Data>Data 1</Data>
	</ID>

	<ID ID="Unique2">
		<Data>Data 2</Data>
	</ID>
</my_Ribbon>

Open in new window

And, here is what I want to add to the xml file:
	<ID ID="UniqueNew">
		<Data>Data New</Data>
	</ID>

Open in new window


But here is what I end up with (xmlns=""):
<ID xmlns="" ID="UniqueNew">
<Data>Data New</Data>
</ID>

Open in new window


I'd be able to live with it, but the addition of: xmlns=""
- stops code that I use to read the xml file from working, and
- adds unwanted junk to my xml file

Function readRibbon(id As String, Language As String, myXML As CustomXMLParts) As String
On Error GoTo 0
' stops working with xmlns=""  :-(
    readRibbon = myXML(1).SelectSingleNode _
    ("/ns0:PT_Ribbon[1]/ns0:ID[@ID='" & id & "']/ns0:Data[1]").Text

lbl_Exit:
    Exit Function
eh:
    readRibbon = ""
    GoTo lbl_Exit
End Function

Open in new window


And, finally, the code I need help with...
Set myXML = ThisDocument.CustomXMLParts.SelectByNamespace("http://www.drTribos/Ribbon/EngAU")

Dim strSubTree As String

strSubTree = "<ID ID=""Grrrr""><Data>MyNewData</Data></ID>"

myXML(1).SelectSingleNode("/ns0:my_Ribbon[1]").AppendChildSubtree (strSubTree)

Open in new window


Please help!

Cheers,
LVL 15
DrTribosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Have you tried adding the branch in two steps instead of one?

Rather than add everything with the string, create the ID element with its ID attribute and then add a child element under the newly created ID element.
0
DrTribosAuthor Commented:
Hi - my xml knowledge is so lame... can you pls provide an example of the code to get me started :-)

Many thanks :-)
0
ste5anSenior DeveloperCommented:
Please post a concise and complete example, thus a minimal document with your sample data and code.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
What is CustomXMLParts?
0
ste5anSenior DeveloperCommented:
A Word and Excel specific XML storage in the document: Custom XML Parts Overview
0
DrTribosAuthor Commented:
Hi - a sample document is attached as requested, rename .zip to .docm

Aikimark - MS Word has the ability to store xml files in a Word document / template.  The collection of these is CustomXMLParts and an item is a CustomXMLPart.  In the IDE it requires a ref to MicrosoftXML, 3.0 +   Also, the XML can be used for all sorts of purposes and does not get wiped by the previous custom XML issues relating to MS infringing on the i4i patent a few years back.

Ste5an - will take a look, I've already read heaps...

Anyway, here is the code in the sample document:

Option Explicit

'Steps:
'1. Create & save the XML file in a txt editor:
'<?xml version="1.0" standalone="yes"?>
'<my_Ribbon xmlns="http://www.drTribos/Ribbon/EngAU" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
'    <ID ID="Unique1">
'        <Data>Data 1</Data>
'    </ID>
'    <ID ID="Unique2">
'        <Data>Data 2</Data>
'    </ID>
'</my_Ribbon>

'2. Add ref to VBA Lib: Microsoft XML, 3.0

'3. Add (load) the XML into the word document as a custom XML part using code like this:
Sub LoadmyXML()
Dim strPath As String
Dim xmlPart As CustomXMLPart
strPath = ThisDocument.Path & "\datastore\myXML.xml"
Set xmlPart = ThisDocument.CustomXMLParts.Add
xmlPart.Load strPath
End Sub

'4. Select the newly added XML file (assumes there is only 1 copy)
Sub DebugXML()
Dim oXMLPart As CustomXMLPart
Set oXMLPart = ActiveDocument.CustomXMLParts.SelectByNamespace("http://www.drTribos/Ribbon/EngAU").Item(1)
Debug.Print oXMLPart.XML
End Sub

'5. Append & check the newly added XML file
Sub AppendXML()
Dim myXML As CustomXMLParts
Set myXML = ThisDocument.CustomXMLParts.SelectByNamespace("http://www.drTribos/Ribbon/EngAU")
Dim strSubTree As String
strSubTree = "<ID ID=""Grrrr""><Data>MyNewData</Data></ID>"
myXML(1).SelectSingleNode("/ns0:my_Ribbon[1]").AppendChildSubtree (strSubTree)
Debug.Print myXML(1).XML
End Sub

Open in new window

EE-Example-XML.zip
0
aikimarkCommented:
I haven't finished testing the MSXML2 version of the solution. In the mean time, I thought you could use an alternative solution in case you were pressed for time.

For this specific problem, you could do this:
Sub InsertXML()
    Dim strXML As String
    Dim intFN As Integer
    Const cEndTag As String = "</my_Ribbon>"
    intFN = FreeFile
    Open ThisDocument.Path & "\datastore\myXML.xml" For Input As #intFN
    strXML = Input(LOF(intFN), #intFN)
    Close
    strXML = Replace(strXML, cEndTag, vbTab & "<ID ID=""Grrrr""><Data>MyNewData</Data></ID>" & vbCrLf & cEndTag)
    Open ThisDocument.Path & "\datastore\myXML.xml" For Output As #intFN
    Print #intFN, strXML;
    Close
End Sub

Open in new window


A more generalized version where you pass in salient values:
Sub InsertXML(ByVal parmEndTag As String, ByVal parmXML As String)
    Dim strXML As String
    Dim intFN As Integer
    intFN = FreeFile
    Open ThisDocument.Path & "\datastore\myXML.xml" For Input As #intFN
    strXML = Input(LOF(intFN), #intFN)
    Close
    strXML = Replace(strXML, parmEndTag, vbTab & parmXML & vbCrLf & parmEndTag)
    Open ThisDocument.Path & "\datastore\myXML.xml" For Output As #intFN
    Print #intFN, strXML;
    Close
End Sub

Open in new window

You might invoke this version like this:
InsertXML "</my_Ribbon>", "<ID ID=""Grrrr""><Data>MyNewData</Data></ID>"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrTribosAuthor Commented:
Hi aikimark,

Thanks for this, it looks like something that will do the trick.... I'd really like to be able to simply append to (or even insert into) the file but this will get me out of strife for the moment.  Will try to implement in the next few days.

Will be keen to see how you go regarding:  
I haven't finished testing the MSXML2 version of the solution.

Cheers,
0
DrTribosAuthor Commented:
Hi aikimark - well it's not quite the solution that I was looking for but it definitely works...  

I ended up taking a shortcut and loading a well formed string of xml directly into a newly added customXMLpart.  Seems like the wrong way of doing things, but I'll deal with that later.  In the meanwhile, please let me know if you get the other part of your testing completed.

Cheers,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.