CODEWITHSUNDEEP
xmlvbadeclaration
Tim Schimandle
Tim Schimandle

Reputation: 802

How can I use vba to modify xml declarations

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>

Upvotes: 1

Views: 3770

Answers (1)

Tim Schimandle
Tim Schimandle

Reputation: 802

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.

Upvotes: 2

Related Questions