Solved

VBA to add XML to XML file in MS Word

Posted on 2014-10-28
10
404 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 14

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

Expert Comment

by:aikimark
ID: 40413238
What is CustomXMLParts?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 33

Expert Comment

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

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 14

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 14

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now