Solved

VBA to add XML to XML file in MS Word

Posted on 2014-10-28
10
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 34

Expert Comment

by:ste5an
ID: 40413175
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!

 
LVL 45

Expert Comment

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

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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

705 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