• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

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,
0
DrTribos
Asked:
DrTribos
  • 4
  • 3
  • 2
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now