Solved

Converting XML to Microsoft Excel

Posted on 2000-05-17
8
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

628 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