Solved

Writing a new record to xml file with VBA

Posted on 2006-06-27
26
2,116 Views
Last Modified: 2013-11-19
Hello Experts,
i have vba that i want to use to write to my xml file.  I am reading from the xml file just fine. I am confused on a couple things.
for one in my xml file i have spaces in the tags.  The book i have had spaces so i thought no big deal.  Evidently they are okay to use becuase i am reading from it fine.  Anyways here is my XML schema.  What I am doing is adding a new <Structure Name> a new <Surveys year> and New <Station> and New <Elevation>

<?xml version="1.0"?>
<!DOCTYPE Bridge_Scour SYSTEM "Bridge_Scour.dtd">
<Bridge_Scour>
     <Structure Name="uw1.dgn">
          <Surveys year="2000">
                <Survey>
                    <Entry><Station>0</Station> <Elevation>926.7</Elevation></Entry>
                    <Entry><Station>17</Station> <Elevation>925.1</Elevation></Entry>
               </Survey>
          </Surveys>
          <Surveys year="2001">
                <Survey>
                    <Entry><Station>0.3</Station> <Elevation>123.47</Elevation></Entry>
                    <Entry><Station>0.4</Station> <Elevation>123.48</Elevation></Entry>
               </Survey>
          </Surveys>
     <Structure Name="uw2.dgn">
          <Surveys year="2001">
                <Survey>
                    <Entry><Station>0.5</Station> <Elevation>123.49</Elevation></Entry>
                    <Entry><Station>0.6</Station> <Elevation>123.50</Elevation></Entry>
               </Survey>
          </Surveys>
     </Structure>
</Bridge_Scour>

Here is my VBA CODE

Sub AddNewEntry(oStructure As String, oYear As String, oflxGrid As MSFlexGrid)
    Dim oSMSlistNode As MSXML2.IXMLDOMElement
    Dim oNewNode As MSXML2.IXMLDOMNode                          'Structure Name
   
    Dim oNEWAuthorisedSenderElement As MSXML2.IXMLDOMElement 'Bridge_Scour
    Dim oNEWStructureElement As MSXML2.IXMLDOMElement           'Name
    Dim oNEWSurveyYearElement As MSXML2.IXMLDOMElement          'Year
    Dim oNewStationAttr As MSXML2.IXMLDOMAttribute                  'Station
    Dim onewElevationAttr As MSXML2.IXMLDOMAttribute                'Elevation
   
    Dim oNewStructure As MSXML2.IXMLDOMText  'oStructure
    Dim oNewYear As MSXML2.IXMLDOMText          'oYear
    Dim oNewStation As MSXML2.IXMLDOMText       'oflexGrid data
    Dim onewElevation As MSXML2.IXMLDOMText     'oflexgrid data
   
   
    myXML.async = False

    Set oSMSlistNode = myXML.documentElement
    Set oNewNode = myXML.selectSingleNode("Bridge_Scour/Structure")
    '[@Name='" & oStructure & "']/Surveys[@year='" & oYear & "']")
    '/Survey/Entry")
   
    Set oNEWAuthorisedSenderElement = myXML.CreateElement("Bridge_Scour")
    Set oNEWStructureElement = myXML.CreateElement("Structure") 'What about the space
    Set oNEWSurveyYearElement = myXML.CreateElement("year")     'What about the space
   
    Set oNewStationAttr = myXML.createAttribute("Station")
    Set onewElevationAttr = myXML.createAttribute("Elevation")
   
    Set oNewStructure = myXML.createTextNode(oStructure)
    Set oNewYear = myXML.createTextNode(oYear)
   
    oNEWStructureElement.appendChild oNewStructure
    oNEWSurveyYearElement.appendChild oNewYear
   
    oNEWAuthorisedSenderElement.appendChild oNEWStructureElement
    oNEWAuthorisedSenderElement.appendChild oNEWSurveyYearElement
   
    oNewNode.appendChild oNEWAuthorisedSenderElement  '<Fails here "Object Not set
   
    myXML.Save "W:\Bridge Inspection\Special Inspection - Drawings\UW - Drawings\Bridge_Scour.xml"
   
    MsgBox "Structure Name " & oStructure & vbCr & "Year " & oYear

'Haven't got this part yet    
'    For i = 1 To oflxGrid.Rows - 2
 '       MsgBox "Station " & oflxGrid.TextMatrix(i, 0) & "Elevation " & oflxGrid.TextMatrix(i, 1)
 '   Next
   
End Sub

Sorry for all the code and XML i am hoping it will explain it better

0
Comment
Question by:sherrick123
  • 12
  • 12
26 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
You don't need to specifically add the whitespace. The whitespace in the XML you posted is between the tags rather than being part of the tag, so it is simply ignored by the parser.
0
 

Author Comment

by:sherrick123
Comment Utility
okay, Well how come when i do write to the file it puts everything in one line.  Here is the partial output of the xml

       <?xml version="1.0"?>
<!DOCTYPE Bridge_Scour SYSTEM "Bridge_Scour.dtd">
<Bridge_Scour>
     <Structure Name="uw1.dgn">
          <Surveys year="2000">
                <Survey>
                    <Entry><Station>0</Station> <Elevation>926.7</Elevation></Entry>
                    <Entry><Station>17</Station> <Elevation>925.1</Elevation></Entry>
               </Survey>
          </Surveys>
          <Surveys year="2001">
                <Survey>
                    <Entry><Station>0.3</Station> <Elevation>123.47</Elevation></Entry>
                    <Entry><Station>0.4</Station> <Elevation>123.48</Elevation></Entry>
               </Survey>
          </Surveys>
     <Name><Structure>uw011072.dgn</Structure><Surveys>2006</Surveys></Name></Structure>   <<======THE LINE I ADDED??
     <Structure Name="uw2.dgn">
          <Surveys year="2001">
                <Survey>
                    <Entry><Station>0.5</Station> <Elevation>123.49</Elevation></Entry>
                    <Entry><Station>0.6</Station> <Elevation>123.50</Elevation></Entry>
               </Survey>
          </Surveys>
     </Structure>
</Bridge_Scour>

How do i get it to be formated correctly???
It should be Structure Name= etc...

Thanks
 
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Looking at your code you appear to be getting confused about what is a Node and what is an Attribute.

"Name" is an attribute of "Structure", "year" is an attribute of "Surveys". "Station" and "Elevation" are both nodes. The fact that they are all on the same line is pretty irrelevant, its the relation of the nodes to each other that is the important thing. If you open your XML file in Wordpad you might get it in a single line, if you open it in IE you should get it displayed in a nested format.

As I mentioned in my last post, the whitespace in between nodes is ignored by the parser.
0
 

Author Comment

by:sherrick123
Comment Utility
okay,
So should i define the Name and Structure as MSXML2.IXMLDOMAttribute

and then what method do i use. is the myXML.CreateElement still correct?


As you can probably tell this is my first expeirence working with XML its a little confusing.

Thanks
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Structure is a node, Name is an attribute of that node.

I don't have VBA installed so this may need tweaking:

    Dim oStruct As IXMLDOMNode
    Dim oName As IXMLDOMAttribute    

    Set oStruct = myXML.createElement("Structure")
    Set oName = myXML.createAttribute("Name")
    oName.Text = "A Name"

    oStruct.setAttributeNode oName
0
 

Author Comment

by:sherrick123
Comment Utility
i will give it a try

Thanks
\
0
 

Author Comment

by:sherrick123
Comment Utility
setAttibuteNode is not a member of a IXMLDOMNode
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Theres an "r" missing out of that. Is that a typo on my part or yours ?
0
 

Author Comment

by:sherrick123
Comment Utility
mine,
But their is still no method like that for a node
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Well there in lies the disadvantage of trying to write code from memory ;o)

The following is simpler anyway:

    Dim oStruct As IXMLDOMNode

    Set oStruct = myXML.createElement("Structure")      '// Create "Structure" node
    oStruct.setAttribute "Name", "A Name"                     '// Add a "Name" attribute
0
 

Author Comment

by:sherrick123
Comment Utility
Their is no setattribute for a node??
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Yes there is. Paste the following in to a file and save it as test.vbs, the run it:

Set oDom = CreateObject("MSXML2.DOMDocument.4.0")
oDom.LoadXML "<root />"

Set oRoot = oDom.documentElement
Set oNew = oDom.createElement("Wibble")

oRoot.appendChild oNew
oNew.setAttribute "Name", "Bob"

MsgBox oDom.Xml
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:sherrick123
Comment Utility
oNew is a Element not a Node,
Here is how i am loading my xml file

Public myXML As New MSXML2.DOMDocument40

myXML.Load (oUWpath & "\" & oXMLFileName)

and with my schema

<?xml version="1.0"?>
<!DOCTYPE Bridge_Scour SYSTEM "Bridge_Scour.dtd">
<Bridge_Scour>
     <Structure Name="uw1.dgn">
          <Surveys year="2000">
                <Survey>
                    <Entry><Station>0</Station> <Elevation>926.7</Elevation></Entry>
                    <Entry><Station>17</Station> <Elevation>925.1</Elevation></Entry>
               </Survey>
          </Surveys>
          <Surveys year="2001">
                <Survey>
                    <Entry><Station>0.3</Station> <Elevation>123.47</Elevation></Entry>
                    <Entry><Station>0.4</Station> <Elevation>123.48</Elevation></Entry>
               </Survey>
          </Surveys>
     </Structure>
</Bridge_Scour>

AND WITH MY VARIABLES DECLARED AS
    Dim oNEWAuthorisedSenderElement As MSXML2.IXMLDOMElement 'Bridge_Scour
    Dim oNEWStructureElement As MSXML2.IXMLDOMElement           'Name
    Dim oNEWSurveyYearElement As MSXML2.IXMLDOMElement          'Year
    Dim oNewStationAttr As MSXML2.IXMLDOMAttribute                  'Station
    Dim onewElevationAttr As MSXML2.IXMLDOMAttribute                'Elevation
   
    Dim oNewStructureName As MSXML2.IXMLDOMAttribute  'oStructure
    Dim oNewYear As MSXML2.IXMLDOMText          'oYear
    Dim oNewStation As MSXML2.IXMLDOMText       'oflexGrid data
    Dim onewElevation As MSXML2.IXMLDOMText     'oflexgrid data

Where does the oRoot fall into this and how can i add a new record.  

I am getting confused on Nodes, Elements and Attributes

Thank you
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Are you trying to add a new Survey or a new Entry under an existing Survey ?
0
 

Author Comment

by:sherrick123
Comment Utility
Okay here is the scoop.

First:       I need to add a NEW structure with a survey year and Stations and Elevations
Second:   I need to add a Survey year and Station and Elevations to and EXSITNGING structure Record
Third:      I will need to possibly edit the Station and Elevations of a Survey record

But for now i need to understand how to ADD a new record/Node to the XML file

Thanks agian,
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Ok, using your Second option as an example (an using different variable names for clarity):

    '// Get the node that we are going to be adding children to. In this case the Surveys node with a "year" of "2000"
    '// I usually call this oRoot because it is the node that will be at the root of the nodes we are adding
    Dim oRoot As IXMLDOMElement
    Set oRoot = myXML.selectSingleNode("Bridge_Scour/Structure[@Name='uw1.dgn']/Surveys[@year='2000']")

    '// The snippet of XML we are working with now looks like:
    '//    <Surveys year="2000" />

    '// Next we create a new Survey node because this is the parent "Entry" node, and we append it.
    '// Because we don't need to assign a text value to the "Survey" node, we can do it all in one line, which saves us
    '// having to use lots of different variables
    Dim oTemp As IXMLDOMElement
    Set oTemp = oRoot.appendChild(myXML.createElement("Survey"))

    '// The snippet of XML we are working with now looks like:
    '//    <Surveys year="2000">
    '//        <Survey />
    '//    </Surveys>

    '// Now, using oSurvey as our root node we create and append a new "Entry" node, because "Entry" is the parent of "Station" and "Elevation".
    '// Again, we don't need to assign a value so we can do it in one line
    Set oTemp = oTemp.appendChild(myXML.createElement("Entry"))

    '// The snippet of XML we are working with now looks like:
    '//    <Surveys year="2000">
    '//        <Survey>
    '//            <Entry />
    '//        </Survey>
    '//    </Surveys>

    '// Now, using the "Entry" node as our root we create and append the "Station" and "Elevation" nodes. Because we are assigning values to them
    '// we store them in seperate variables rather than reusing oTemp
    Dim oStation As IXMLDOMNode
    Set oStation = myXML.createElement("Station")
    oStation.Text = "I am a new station"
    oTemp.appendChild oStation

    Dim oElevation As IXMLDOMNode
    Set oElevation = myXML.createElement("Elevation")
    oElevation.Text = "I am a new elevation"
    oTemp.appendChild oElevation

    '// The snippet of XML we are working with now looks like:
    '//    <Surveys year="2000">
    '//        <Survey>
    '//            <Entry>
    '//                <Station>I am a new station</Station>
    '//                <Elevation>I am a new elevation</Elevation>
    '//            </Entry>
    '//        </Survey>
    '//    </Surveys>
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Typo:

    '// Now, using oSurvey as our root node we create and append a new "Entry" node, because "Entry" is the parent of "Station" and "Elevation".

Should say:

    '// Now, using oTemp as our root node we create and append a new "Entry" node, because "Entry" is the parent of "Station" and "Elevation".
0
 

Author Comment

by:sherrick123
Comment Utility
Running into a problem,
Because i am adding a year that does not exist yet for a structure the variable oRoot is equal to nothing.

    Set oRoot = myXML.selectSingleNode("Bridge_Scour/Structure[@Name='" & oFile & "']/Surveys[@year='" & oYear & "']")

it looks like i just need to set the oroot as
Set oRoot = myXML.selectSingleNode("Bridge_Scour/Structure[@Name='" & oFile & "']")

how will that effect the rest of the code.??
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
The sample I posted was just to show the general procedure based on your second sample. If you concentrate on the steps to create a single element and add it to a node it, hopefully, should become easier.

As far as your question goes; you are correct. If you need to add a new Survey node then you first need to grab the Structure node to which you want to add the new Survey, which is what you are doing with:

    Set oRoot = myXML.selectSingleNode("Bridge_Scour/Structure[@Name='" & oFile & "']")

So now you follow the same routine as before for adding a new node. Only this time it is slightly different because you also need to add an Attribute to the Survey node you will be creating:

    '// Create a new Survey node
    Dim oSurvey As IXMLDOMElement
    Set oSurvey = myXML.createElement("Surveys")

    '// Add a "year" attribute
    oSurvey.setAttribute "year", "2006"

    '// Append to our root node
    oRoot.appendChild oSurvey

Then when you come to create the "Survey" node you simply append it to oSurvey rather than oRoot.
0
 

Author Comment

by:sherrick123
Comment Utility
Getting really close here is the output

  <Surveys year="2006" />
         - <Survey>
             - <Entry>
                   <Station>0</Station>
                    <Elevation>15</Elevation>
              - <Entry>
                    <Station>1</Station>
                   <Elevation>18</Elevation>
              - <Entry>
                       <Station>2</Station>
                        <Elevation>19</Elevation>
              </Entry>
          </Entry>
        </Entry>
     </Survey>
  </Structure>

as you can see it is not putting the </Entry> where is should be and it also never closes the Surverys </Surveys>

Here it the exact code i am using



Sub AddEditNewSurvey(oFile As String, oYear As String, oflx As MSFlexGrid)
 '// Get the node that we are going to be adding children to. In this case the Surveys node with a "year" of "2000"
    '// I usually call this oRoot because it is the node that will be at the root of the nodes we are adding
    Dim oRoot As IXMLDOMElement
    Set oRoot = myXML.selectSingleNode("Bridge_Scour/Structure[@Name='" & oFile & "']")
    '/Surveys[@year='" & oYear & "']")

    '// Adding the Surveys year element
    '// The snippet of XML we are working with now looks like:
    '//    <Surveys year="2000" />
    '// Create a new Survey node
    Dim oSurvey As IXMLDOMElement
    Set oSurvey = myXML.CreateElement("Surveys")

    '// Add a "year" attribute
    oSurvey.setAttribute "year", oYear

    '// Append to our root node
    oRoot.appendChild oSurvey

   

    '// Next we create a new Survey node because this is the parent "Entry" node, and we append it.
    '// Because we don't need to assign a text value to the "Survey" node, we can do it all in one line, which saves us
    '// having to use lots of different variables
    Dim otemp As IXMLDOMElement
    Set otemp = oRoot.appendChild(myXML.CreateElement("Survey"))

    '// The snippet of XML we are working with now looks like:
    '//    <Surveys year="2000">
    '//        <Survey />
    '//    </Surveys>

     '// Now, using oTemp as our root node we create and append a new "Entry" node, because "Entry" is the parent of "Station" and "Elevation".
    '// Again, we don't need to assign a value so we can do it in one line
    'Set otemp = otemp.appendChild(myXML.CreateElement("Entry"))

    '// The snippet of XML we are working with now looks like:
    '//    <Surveys year="2000">
    '//        <Survey>
    '//            <Entry />
    '//        </Survey>
    '//    </Surveys>

    '// Now, using the "Entry" node as our root we create and append the "Station" and "Elevation" nodes. Because we are assigning values to them
    '// we store them in seperate variables rather than reusing oTemp
    Dim oStation As IXMLDOMNode
    Dim oElevation As IXMLDOMNode

    Dim i As Integer

    For i = 1 To oflx.Rows - 2
        Set otemp = otemp.appendChild(myXML.CreateElement("Entry"))
        Set oStation = myXML.CreateElement("Station")
        oStation.Text = Val(oflx.TextMatrix(i, 0)) 'Station Row 0
        otemp.appendChild oStation
   
        Set oElevation = myXML.CreateElement("Elevation")
        oElevation.Text = oflx.TextMatrix(i, 1)
        otemp.appendChild oElevation
       

    Next

    '// The snippet of XML we are working with now looks like:
    '//    <Surveys year="2000">
    '//        <Survey>
    '//            <Entry>
    '//                <Station>I am a new station</Station>
    '//                <Elevation>I am a new elevation</Elevation>
    '//            </Entry>
    '//        </Survey>
    '//    </Surveys>
   
    myXML.Save "W:\Bridge Inspection\Special Inspection - Drawings\UW - Drawings\Bridge_Scour.xml"

End Sub


0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Thats because you are currently overwriting oTemp with the new Entry node, so that the Entry node is being appended to rather than the Survey node.

So, change this block:

   Dim i As Integer

    For i = 1 To oflx.Rows - 2
        Set otemp = otemp.appendChild(myXML.CreateElement("Entry"))
        Set oStation = myXML.CreateElement("Station")
        oStation.Text = Val(oflx.TextMatrix(i, 0)) 'Station Row 0
        otemp.appendChild oStation
   
        Set oElevation = myXML.CreateElement("Elevation")
        oElevation.Text = oflx.TextMatrix(i, 1)
        otemp.appendChild oElevation
    Next

To:

   Dim oEntry As IXMLDOMElement
   Dim i As Integer

    For i = 1 To oflx.Rows - 2
        Set oEntry = otemp.appendChild(myXML.CreateElement("Entry"))
        Set oStation = myXML.CreateElement("Station")
        oStation.Text = Val(oflx.TextMatrix(i, 0)) 'Station Row 0
        oEntry.appendChild oStation
   
        Set oElevation = myXML.CreateElement("Elevation")
        oElevation.Text = oflx.TextMatrix(i, 1)
        oEntry.appendChild oElevation
    Next

That way oTemp continues to point at the Survey node to be used to append the Entry nodes to, and oEntry is used to append the Station and Elevation nodes to.
0
 

Author Comment

by:sherrick123
Comment Utility
So close now It's still not closing the surveys node </Surveys>

0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
Comment Utility
Oh, didn't notice that bit. You need to change:

    '// Next we create a new Survey node because this is the parent "Entry" node, and we append it.
    '// Because we don't need to assign a text value to the "Survey" node, we can do it all in one line, which saves us
    '// having to use lots of different variables
    Dim otemp As IXMLDOMElement
    Set otemp = oRoot.appendChild(myXML.CreateElement("Survey"))

To:

    '// Next we create a new Survey node because this is the parent "Entry" node, and we append it.
    '// Because we don't need to assign a text value to the "Survey" node, we can do it all in one line, which saves us
    '// having to use lots of different variables
    Dim otemp As IXMLDOMElement
    Set otemp = oSurvey.appendChild(myXML.CreateElement("Survey"))


oRoot is the Structure node. You add a Survey node to it, currently you are adding the Survey node to oRoot (the "Structure" node) rather than oSurvey (the "Surveys" node).
0
 

Author Comment

by:sherrick123
Comment Utility
Thank you so much
Now i am going to go through this and try to figure out the Node The Elements and Attributes.  This is my first time of working with xml.  It's differente then databases.

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

771 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

11 Experts available now in Live!

Get 1:1 Help Now