Link to home
Start Free TrialLog in
Avatar of vrajvilas
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</TotalVisits>
          <LatestVisit>1/4/2000</LatestVisit>
        </Country>
       </SiteVisits>

Desired ouput xml:

<?xml version="1.0"?>
      <SiteVisits Read="True">
        <Country CountryName="USA">
          <TotalVisits>1348</TotalVisits>
          <LatestVisit>1/4/2000</LatestVisit>
        </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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vrajvilas
vrajvilas

ASKER

Thanks a lot that worked, iam going to give you the full points. But i do have a question if i want to change the values of a particular element see below the TotalVisits in the desired ouptput is changed and save it that would be great.  

input xml :
<?xml version="1.0"?>
      <SiteVisits>
        <Country CountryName="USA">
          <TotalVisits>1348</TotalVisits>
          <LatestVisit>1/4/2000</LatestVisit>
        </Country>
       </SiteVisits>

Desired ouput xml:

<?xml version="1.0"?>
      <SiteVisits Read="True">
        <Country CountryName="USA">
          <TotalVisits>50000000000</TotalVisits>
          <LatestVisit>1/4/2000</LatestVisit>
        </Country>
       </SiteVisits>
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
 

Open in new window

Thank you very much