We help IT Professionals succeed at work.
Get Started

XML Manipulation in VBA

vrajvilas
vrajvilas asked
on
2,984 Views
Last Modified: 2013-12-20
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
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE