Solved

Converting XML to Microsoft Excel

Posted on 2000-05-17
8
243 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
Technology Partners: 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!

 
LVL 1

Expert Comment

by:pradeepgwalani
ID: 2833529
Use XML Spy and have it export / import data from datasources.
0
 
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
XML Data Missing in PHP SimpleXML 8 83
How to write an English digest paper 6 98
Macro to import XML in Access 2013 2 63
PowerShell script to remove string in xml file 3 40
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

763 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