vrajvilas
asked on
XML Manipulation in VBA
Hi all,
I have a xml file and some how i could read the xml file through vba and below is the vba program that does reading of the file, once read i dont know how to appened the attributes to the root node with read = "true" (indicating that the xml file is read and processed). Below is the the input xml and the desired output xml, and the attached code to read the inputxml. It is highly appreciated if you can modify the attached code to get the desired output xml and save it .
input xml :
<?xml version="1.0"?>
<SiteVisits>
<Country CountryName="USA">
<TotalVisits>1348</TotalVi sits>
<LatestVisit>1/4/2000</Lat estVisit>
</Country>
</SiteVisits>
Desired ouput xml:
<?xml version="1.0"?>
<SiteVisits Read="True">
<Country CountryName="USA">
<TotalVisits>1348</TotalVi sits>
<LatestVisit>1/4/2000</Lat estVisit>
</Country>
</SiteVisits>
I have a xml file and some how i could read the xml file through vba and below is the vba program that does reading of the file, once read i dont know how to appened the attributes to the root node with read = "true" (indicating that the xml file is read and processed). Below is the the input xml and the desired output xml, and the attached code to read the inputxml. It is highly appreciated if you can modify the attached code to get the desired output xml and save it .
input xml :
<?xml version="1.0"?>
<SiteVisits>
<Country CountryName="USA">
<TotalVisits>1348</TotalVi
<LatestVisit>1/4/2000</Lat
</Country>
</SiteVisits>
Desired ouput xml:
<?xml version="1.0"?>
<SiteVisits Read="True">
<Country CountryName="USA">
<TotalVisits>1348</TotalVi
<LatestVisit>1/4/2000</Lat
</Country>
</SiteVisits>
Private Sub cmdLoad_Click()
Dim oDoc As DOMDocument60
Dim fSuccess As Boolean
Dim oRoot As IXMLDOMNode
Dim oCountry As IXMLDOMNode
Dim oAttributes As IXMLDOMNamedNodeMap
Dim oCountryName As IXMLDOMNode
Dim oChildren As IXMLDOMNodeList
Dim oChild As IXMLDOMNode
Dim intI As Integer
Dim XMLString As String
On Error GoTo HandleErr
Set oDoc = New DOMDocument60
oDoc.async = False
oDoc.validateOnParse = False
fSuccess = oDoc.Load("c:\traffic1.xml")
' If anything went wrong, quit now.
If Not fSuccess Then
GoTo ExitHere
End If
' Get the root of the XML tree.
Set oRoot = oDoc.documentElement
' Go through all children of the root.
For Each oCountry In oRoot.childNodes
' Collect the attributes for this country/region.
Set oAttributes = oCountry.Attributes
' Extract the country/region name and
Set oCountryName = oAttributes.getNamedItem("CountryName")
Debug.Print oCountryName.Text
' Go through all the children of the country/region node.
Set oChildren = oCountry.childNodes
For Each oChild In oChildren
If oChild.nodeName = "TotalVisits" Then
Debug.Print oChild.nodeTypedValue
End If
If oChild.nodeName = "LatestVisit" Then
Debug.Print oChild.nodeTypedValue
End If
Next oChild
Next oCountry
ExitHere:
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitHere
Resume
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent
This will check for Country of USA and if so ... set totalvisits.
Private Sub cmdLoad_Click()
Dim oDoc As DOMDocument60
Dim fSuccess As Boolean
Dim oRoot As IXMLDOMNode
Dim oCountry As IXMLDOMNode
Dim oAttributes As IXMLDOMNamedNodeMap
Dim oCountryName As IXMLDOMNode
Dim oChildren As IXMLDOMNodeList
Dim oChild As IXMLDOMNode
Dim intI As Integer
Dim XMLString As String
Dim oSiteVisits As IXMLDOMElement
On Error GoTo HandleErr
Set oDoc = New DOMDocument60
oDoc.async = False
oDoc.validateOnParse = False
fSuccess = oDoc.Load("c:\traffic1.xml")
' If anything went wrong, quit now.
If Not fSuccess Then
GoTo ExitHere
End If
' Get the root of the XML tree.
Set oRoot = oDoc.DocumentElement
' Go through all children of the root.
For Each oCountry In oRoot.ChildNodes
' Collect the attributes for this country/region.
Set oAttributes = oCountry.Attributes
' Extract the country/region name and
Set oCountryName = oAttributes.getNamedItem("CountryName")
Debug.Print oCountryName.Text
' Go through all the children of the country/region node.
Set oChildren = oCountry.ChildNodes
For Each oChild In oChildren
If oChild.nodeName = "TotalVisits" Then
Debug.Print oChild.nodeTypedValue
If oCountryName.NodeValue = "USA" Then
oChild.Text = "50000000000"
End If
End If
If oChild.nodeName = "LatestVisit" Then
Debug.Print oChild.nodeTypedValue
End If
Next oChild
Next oCountry
Set oSiteVisits = oDoc.getElementsByTagName("SiteVisits").Item(0)
oSiteVisits.setAttribute "Read", "True"
oDoc.Save ("c:\traffic2.xml")
ExitHere:
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitHere
Resume
End Sub
ASKER
Thank you very much
ASKER
input xml :
<?xml version="1.0"?>
<SiteVisits>
<Country CountryName="USA">
<TotalVisits>1348</TotalVi
<LatestVisit>1/4/2000</Lat
</Country>
</SiteVisits>
Desired ouput xml:
<?xml version="1.0"?>
<SiteVisits Read="True">
<Country CountryName="USA">
<TotalVisits>50000000000</
<LatestVisit>1/4/2000</Lat
</Country>
</SiteVisits>