Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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?
0
ccherry13
Asked:
ccherry13
1 Solution
 
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
 
bruintjeCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now