We help IT Professionals succeed at work.

XML Manipulation in VBA

vrajvilas
vrajvilas asked
on
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

Comment
Watch Question

View Solution Only

Commented:
The key to this is to define an IXMLDOMElement instead of an IXMLDOMNode.  (See Attached Code)

I saved the result to a different file.  It's perfectly alright to save back to the same file you read from as well.

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

vrajvilas

Author

Commented:
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>
vrajvilas

Author

Commented:
Excellent
Ken Butters

Commented:
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

vrajvilas

Author

Commented:
Thank you very much