asked on

Writing a new record to xml file with VBA

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

Carl Tawn

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.

sherrick123

ASKER

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

Carl Tawn

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.

sherrick123

ASKER

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

Carl Tawn

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

sherrick123

ASKER

i will give it a try

Thanks
\

sherrick123

ASKER

setAttibuteNode is not a member of a IXMLDOMNode

Carl Tawn

Theres an "r" missing out of that. Is that a typo on my part or yours ?

sherrick123

ASKER

mine,
But their is still no method like that for a node

Carl Tawn

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

sherrick123

ASKER

Their is no setattribute for a node??

Carl Tawn

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

sherrick123

ASKER

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

Carl Tawn

Are you trying to add a new Survey or a new Entry under an existing Survey ?

sherrick123

ASKER

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,

Carl Tawn

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>

Carl Tawn

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".

sherrick123

ASKER

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.??

Carl Tawn

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.

sherrick123

ASKER

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

Carl Tawn

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.

sherrick123

ASKER

So close now It's still not closing the surveys node </Surveys>

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sherrick123

ASKER

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.