Solved

VBA to add XML to XML file in MS Word

Posted on 2014-10-28
10
435 Views
Last Modified: 2014-11-05
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
Comment
Question by:DrTribos
  • 4
  • 3
  • 2
10 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40411386
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
 
LVL 15

Author Comment

by:DrTribos
ID: 40411694
Hi - my xml knowledge is so lame... can you pls provide an example of the code to get me started :-)

Many thanks :-)
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40413175
Please post a concise and complete example, thus a minimal document with your sample data and code.
0
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
LVL 45

Expert Comment

by:aikimark
ID: 40413238
What is CustomXMLParts?
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40413286
A Word and Excel specific XML storage in the document: Custom XML Parts Overview
0
 
LVL 15

Author Comment

by:DrTribos
ID: 40414320
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
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40415344
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
 
LVL 15

Author Comment

by:DrTribos
ID: 40416870
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
 
LVL 15

Author Closing Comment

by:DrTribos
ID: 40423569
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question