Solved

Converting XML to Microsoft Excel

Posted on 2000-05-17
8
235 Views
Last Modified: 2013-11-19
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
Comment
Question by:ccherry13
8 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 2818572
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
 

Expert Comment

by:mj@f544695
ID: 2822608
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
 
LVL 44

Accepted Solution

by:
bruintje earned 75 total points
ID: 2830949
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
 
LVL 1

Expert Comment

by:pradeepgwalani
ID: 2833529
Use XML Spy and have it export / import data from datasources.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 44

Expert Comment

by:bruintje
ID: 2859102
Hi again,

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

:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 2945111
and?
0
 

Author Comment

by:ccherry13
ID: 2956892
Comment accepted as answer
0
 

Author Comment

by:ccherry13
ID: 2956893
Excellent, concise answer
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now