XML to Excel

Summary

System Requirement

• Microsoft Windows + Excel + MSXML 2.0

Quick Start

1. Save a program source (conv-xml2xl-1.bas) on your local disk.
2. Start Microsoft Excel with a blank(new) book.
3. Open the Visual Basic Editor. (Tool/Macro/Visual Basic Editor)
4. Import the downloaded program source (conv-xml2xl-1.bas). (File/Import Files)
5. Specify your XML file location to the variable "xmlUrl" at the first line of the main program "okGo" in the imported module.
6. Run "okGo". (Run/Run macro/okGo/Run)
7. View the result on a new book.

Tools

1. Common hints to all tools
   • Tools are served as a text file of VBA source code. You have to import them into your Excel Book before using tools.
   • A typical way to use tools is described above "quick start". This is a common usage for most tools.
   • The URL of an XML document to convert is described explicitly in sources. Customize them as you like.
   • An output is created as a separated new Excel book. Save the book on your disk if you want.
   • It is also a good idea to customize the program as you wish, and save it as an Excel Book for your future uses.
   • Tools are good for both well-formed and valid XMLs.
   • Most tools use late-bindings object. Easy to install/customize, but slower to run (negligible).
2. DOM Tree view (conv-xml2xl-1.bas)
   • convert an XML document into a Microsoft Excel Worksheet
   • Sheet created looks like DOM tree
   • technologies: VBA, DOM
3. Plain Table view (conv-xml2xl-2.bas)
   • convert an XML document into a Microsoft Excel Worksheet
   • Sheet created as a simple table
   • technologies: VBA, DOM
4. Multi-Sheet view (conv-xml2xl-3.bas)
   • convert an XML document into a Microsoft Excel Worksheet
   • Many sheets are created according to the DOM tree
   • how to start
     1. Save a program source (conv-xml2xl-3.bas) on your local disk.
     2. Start Microsoft Excel with a blank(new) book.
     3. Open the Visual Basic Editor. (Tool/Macro/Visual Basic Editor)
     4. Import the downloaded program source (conv-xml2xl-3.bas). (File/Import Files)
     5. Open the VBA source code of Worksheet "Sheet1". (right click the sheet tab, then "View Code".) If you have no "Sheet1", create it now.
     6. Copy next 3 lines and paste them to the [Sheet1 (code)] as a VBA source.

        Sub Show()
            Me.Activate
        End Sub

     7. Save the book as your conv-xml2xl-3 book. After you made it, above procedures are no longer required for the next time.
     8. Specify your XML file location to the variable "xmlUrl" at the first line of the main program "okGo" in the imported module.
     9. Run "okGo". (Tool/Macro/Run macro/okGo/Run)
     10. View the result on a new book.
   • Click lavender cells to jump to the parent sheet or the child sheet.
   • This tools uses Worksheet "Sheet1" as a template sheet. Be sure to keep it in your book as a blank sheet. You are free to add any sheets to customize your book, but keep the "Sheet1" blank.
   • This tool makes many sheets depending on specified XML document. Try with a simple XML at first, and see how it works.
   • technologies: VBA, DOM

History

2000/9/23

conv-xml2xl-2.bas released

conv-xml2xl-3.bas released

2000/9/20

conv-xml2xl-1.bas released

• These programs come with ABSOLUTELY NO WARRANTY. These are free softwares, and you are welcome to redistribute them under certain conditions. See GNU General Public License for details.
• Also read this page about the license.