Converting XML to Microsoft Excel

Is anyone familiar with a readily available object, driver or other means of converting XML to Microsoft .xls format?

Any help would be greatly appreciated?
ccherry13Asked:
Who is Participating?
 
bruintjeConnect With a Mentor Commented:
Hi again,

another sample how to import xml data into excel

http://www.officevba.com/features/2000/03/vba200003mg_f/vba200003mg_f.asp

and here some pieces of it, for the complete sample you can download the xml and excel file from the link

==========
the xml piece
can be saved
as test.xml
==========
<?xml version="1.0"?>

<SiteVisits>

  <Country CountryName="USA">

    <TotalVisits>1348</TotalVisits>

    <LatestVisit>1/4/2000</LatestVisit>

  </Country>

  <Country CountryName="UK">

    <TotalVisits>764</TotalVisits>

    <LatestVisit>1/4/2000</LatestVisit>

  </Country>

  <Country CountryName="Zambia">

    <TotalVisits>42</TotalVisits>

    <LatestVisit>1/4/2000</LatestVisit>

  </Country>

</SiteVisits>

===========
the excel works
===========

open a new document
open the vba editor with ALT+F11
insert a new module
be sure to reference the msxml library

then paste this code

Private Sub test()

    Dim oDoc As MSXML.DOMDocument
    Dim fSuccess As Boolean
    Dim oRoot As MSXML.IXMLDOMNode
    Dim oCountry As MSXML.IXMLDOMNode
    Dim oAttributes As MSXML.IXMLDOMNamedNodeMap
    Dim oCountryName As MSXML.IXMLDOMNode
    Dim oChildren As MSXML.IXMLDOMNodeList
    Dim oChild As MSXML.IXMLDOMNode
    Dim intI As Integer
   
    On Error GoTo HandleErr
   
    Set oDoc = New MSXML.DOMDocument
   
    ' Load the XML from disk, without validating it. Wait for
    ' the load to finish before proceeding.
    oDoc.async = False
    oDoc.validateOnParse = False
    fSuccess = oDoc.Load(ActiveWorkbook.Path & "\test.xml")
   
    ' If anything went wrong, quit now
    If Not fSuccess Then
        GoTo ExitHere
    End If
   
    ' Set up a row counter
    intI = 5
   
    ' Delete the previous information
    ActiveSheet.Cells(4, 1).CurrentRegion.ClearContents
    ActiveSheet.Shapes(2).Delete
   
    ' Create column headers
    ActiveSheet.Cells(4, 1) = "Country"
    ActiveSheet.Cells(4, 2) = "Total Visits"
    ActiveSheet.Cells(4, 3) = "Latest Visit"
   
    ' 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
        Set oAttributes = oCountry.Attributes
        ' Extract the country name and place it on the worksheet
        Set oCountryName = oAttributes.getNamedItem("CountryName")
        ActiveSheet.Cells(intI, 1).Value = oCountryName.Text
        ' Go through all the children of the country node
        Set oChildren = oCountry.childNodes
        For Each oChild In oChildren
            ' Get information from each child node to the sheet
            If oChild.nodeName = "TotalVisits" Then
                ActiveSheet.Cells(intI, 2) = oChild.nodeTypedValue
            End If
            If oChild.nodeName = "LatestVisit" Then
                ActiveSheet.Cells(intI, 3) = oChild.nodeTypedValue
            End If
        Next oChild
        intI = intI + 1
    Next oCountry
   
    ' Now for some eye candy...build a chart of the data
    Charts.Add
    With ActiveChart
        .ChartType = xl3DPieExploded
        .SetSourceData Source:=Sheets("Sheet1").Range("A5:B" & CStr(intI - 1)), PlotBy _
        :=xlColumns
        .Location Where:=xlLocationAsObject, Name:="Sheet1"
    End With
   
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Characters.Text = "Web Site Visits"

    ActiveSheet.Shapes(2).Top = 0
    ActiveSheet.Shapes(2).Left = 200
   
ExitHere:
    Exit Sub
   
HandleErr:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume ExitHere
    Resume
End Sub

=============
then hit F9
=============

the code is well documented so it should not be too hard to follow. this sample is great for trying out some things......

add some nodes to the xml file, create the graph in a seperate sheet etc...

<important>be sure to read the article, and download the sample</important>

HTH:O)Bruintje
0
 
bruintjeCommented:
Hi ccherry13,

You can downlaod the reference for Office2k since it's native HTML/XML

http://msdn.microsoft.com/library/officedev/ofxml2k/ofxml2k.htm

All the sample code you need and a complete guide to the seperate Office components with their respective xml tree's

a few sample pieces from that reference

"
Example

This example shows XML document property elements specifying the authors, the company, the version, and when the document was created, printed, and saved.


><xml>
 <o:DocumentProperties>
  <o:Author>Nancy Davolio</o:Author>
  <o:LastAuthor>Robert King</o:LastAuthor>
  <o:LastPrinted>1999-05-13T01:33:34Z</o:LastPrinted>
  <o:Created>1999-05-13T01:32:34Z</o:Created>
  <o:LastSaved>1999-05-14T21:52:31Z</o:LastSaved>
  <o:Company>Microsoft Corp.</o:Company>
  <o:Version>9.2720</o:Version>
 </o:DocumentProperties>
</xml>

This example shows the contents of the filelist.xml file. It lists the main file, print.htm, and its two supporting files, img001.png and filelist.xml.



 <xml xmlns:o="urn:schemas-microsoft-com:office:office">
  <o:MainFile HRef="../print.htm"/>
  <o:File HRef="img001.png"/>
  <o:File HRef="filelist.xml"/>
 </xml>


The following example shows the XML element in a cachedata.xml file. Five namespaces are specified: dt, rs, s, x, and z.



<xml xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
 xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
 xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
....
</xml>

"

HTH:O)Bruintje
0
 
mj@f544695Commented:
There's also an article on how to do this. Check out http://www.xmlmag.com/upload/free/features/xml/1999/01win99/kc1win99/kc1win99.asp.

--Mark Johnson
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
pradeepgwalaniCommented:
Use XML Spy and have it export / import data from datasources.
0
 
bruintjeCommented:
Hi again,

How did it go??
trying or got some figured out already?

:O)Bruintje
0
 
bruintjeCommented:
and?
0
 
ccherry13Author Commented:
Comment accepted as answer
0
 
ccherry13Author Commented:
Excellent, concise answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.