How can I use vba to modify xml declarations

Question

my first post, woot! I've been lurking for the last 6 months as I have been learning VBA and have made a lot of progress. But now I'm stuck and I need your help!

I have a set of xml documents that I need to replace the declarations at the top with another declaration and I have no idea how to do this. I have read many articles on using DOMDocument in VBA to modify xml documents and I have figured out how to modify different nodes within the xml file with help of the microsoft API. However I cannot seem to access the declarations.

Is this possible?

If so, am I on the right track?

If so, what (if they exist) properties or methods do I need to call to access the declarations in the xml file so that I can modify them?

If not, how should I go about this?

I would really like to avoid doing this by copy and pasting by hand, there are thousands of records that need modification.

Here is my VBA to pull and modify fields:

Sub XMLtest()
    Dim strXML As String
    Dim objXML As MSXML2.DOMDocument
    Dim point As IXMLDOMNode
    Dim origVal As String
    Dim newVal As String

    Set objXML = New MSXML2.DOMDocument

    strXML = "Q:\TIS\!Safety(Beth)\Tim's Projects\Safety Inspections\xml\2011-12-07T10_32_31(good tag).xml"

    objXML.Load (strXML)

    Set point = objXML.DocumentElement.ChildNodes.Item(0)
    Debug.Print point.XML

    origVal = objXML.DocumentElement.ChildNodes.Item(0).Text
    MsgBox origVal

    'this is a section that will change the value of the first item
    objXML.DocumentElement.ChildNodes.Item(0).Text = "TimTest1"
    MsgBox objXML.DocumentElement.ChildNodes.Item(0).Text

    objXML.Save (strXML)

End Sub

My goal is to remove the declarations part of the xml file and replace it with another set of declarations that allow it to be read by infopath again (the current declarations will not be read by the infopath form).

The declarations I want to remove:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:Office-Inspection:-myXSD-2011-10-05T14-49-56" PIVersion="1.0.0.0" href="http://a.octer.com/dept/TIS/TISSafety/Office%20Inspection/Forms/template.xsn" solutionVersion="1.0.0.31" productVersion="14.0.0" ?>

<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>

<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:tns="http://microsoft.com/webservices/SharePointPortalServer/UserProfileService" xmlns:s1="http://microsoft.com/wsdl/types/" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-10-05T14:49:56" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">

Here is an xml document that I am trying to modify:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:Office-Inspection:-myXSD-    2011-10-05T14-49-56" PIVersion="1.0.0.0" href="http://a.octer.com/dept/TIS/TISSafety/Office%20Inspection/Forms/template.xsn" solutionVersion="1.0.0.31" productVersion="14.0.0" ?>

<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>

<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:tns="http://microsoft.com/webservices/SharePointPortalServer/UserProfileService" xmlns:s1="http://microsoft.com/wsdl/types/" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-10-05T14:49:56" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">  

<my:Manager>MikeTest2</my:Manager>
<my:Date>2011-12-07</my:Date>
<my:Team>Marketing</my:Team>
<my:Inspector>HermanTest3</my:Inspector>
<my:Aisles>Y</my:Aisles>
<my:SecondaryAisles>Y</my:SecondaryAisles>
<my:CircutOverload>Y</my:CircutOverload>
<my:OutletCovers>Y</my:OutletCovers>
<my:PanelsClosed>Y</my:PanelsClosed>
<my:FireExt>Y</my:FireExt>
<my:ExtTag>Y</my:ExtTag>
<my:CeilingTiles>Y</my:CeilingTiles>
<my:Sprinklers>Y</my:Sprinklers>
<my:Evacuation>Y</my:Evacuation>
<my:a44444>Y</my:a44444>
<my:Comments>email sent to team encouraging a general cleanup of floor space in cubicles.</my:Comments>
</my:myFields>

will the declarations always be the same? ie the same length and always in first? — Romain
– Romain, Commented Jul 24, 2012 at 9:30
I'll leave the VB code for this to people who know VB, but it's worth pointing out that this ("I have thousands of text files and I need to make a formulaic change to a few lines in each of them") is generally the kind of problem best solved by a Perl/bash/awk one-liner. Cross-training is very beneficial. — Brighid McDonnell
– Brighid McDonnell, Commented Jul 28, 2012 at 5:36

Tim Schimandle · Accepted Answer · 2014-05-28 18:45:34Z

2

So I found the answer:

You need to read the API further here to learn how to use the msxml addon: http://msdn.microsoft.com/en-us/library/ms766487

At which point I used this line of VBA to access the declarations:

objXML.ChildNodes.Item(0)

This is the firt declaration:

<?xml version="1.0" encoding="UTF-8"?>

Basically I needed to learn about the structure of a DOM document and the use of nodes and their properties. Hopefully this will help others out there looking for this answer in the future.

edited May 28, 2014 at 18:45

answered Jul 27, 2012 at 22:12

Tim Schimandle

81212 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I use vba to modify xml declarations

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related