Store XML Data to Excel Sheet using VBScript and HTML5

This article shows how to read a XML document and store the XML data to a Microsoft Excel spreadsheet using VBScript and HTML 5.


Figure 1: XML to Excel

Step 1

1. Start the Excel Application.
   
   
2. Save the empty workbook with the extension “xlsx”.
   
   
3. Close the workbook.
   
   
   Figure 2: Excel application

Step 2

1. Open Notepad.
   
   
2. Add the following code.
   
   1. <?xml version="1.0"?>
   2. <catalog>
   3. <book id="bk101">
   4. <author>Gambardella, Matthew</author>
   5. <title>XML Developer's Guide</title>
   6. <genre>Computer</genre>
   7. <price>44.95</price>
   8. <publish_date>2000-10-01</publish_date>
   9. <description>An in-depth look at creating applications
   10. with XML.</description>
   11. </book>
   12. <book id="bk102">
   13. <author>Ralls, Kim</author>
   14. <title>Midnight Rain</title>
   15. <genre>Fantasy</genre>
   16. <price>5.95</price>
   17. <publish_date>2000-12-16</publish_date>
   18. <description>A former architect battles corporate zombies,
   19. an evil sorceress, and her own childhood to become queen
   20. of the world.</description>
   21. </book>
   22. <book id="bk103">
   23. <author>Corets, Eva</author>
   24. <title>Maeve Ascendant</title>
   25. <genre>Fantasy</genre>
   26. <price>5.95</price>
   27. <publish_date>2000-11-17</publish_date>
   28. <description>After the collapse of a nanotechnology
   29. society in England, the young survivors lay the
   30. foundation for a new society.</description>
   31. </book>
   32. <book id="bk104">
   33. <author>Corets, Eva</author>
   34. <title>Oberon's Legacy</title>
   35. <genre>Fantasy</genre>
   36. <price>5.95</price>
   37. <publish_date>2001-03-10</publish_date>
   38. <description>In post-apocalypse England, the mysterious
   39. agent known only as Oberon helps to create a new life
   40. for the inhabitants of London. Sequel to Maeve
   41. Ascendant.</description>
   42. </book>
   43. <book id="bk105">
   44. <author>Corets, Eva</author>
   45. <title>The Sundered Grail</title>
   46. <genre>Fantasy</genre>
   47. <price>5.95</price>
   48. <publish_date>2001-09-10</publish_date>
   49. <description>The two daughters of Maeve, half-sisters,
   50. battle one another for control of England. Sequel to
   51. Oberon's Legacy.</description>
   52. </book>
   53. <book id="bk106">
   54. <author>Randall, Cynthia</author>
   55. <title>Lover Birds</title>
   56. <genre>Romance</genre>
   57. <price>4.95</price>
   58. <publish_date>2000-09-02</publish_date>
   59. <description>When Carla meets Paul at an ornithology
   60. conference, tempers fly as feathers get ruffled.</description>
   61. </book>
   62. <book id="bk107">
   63. <author>Thurman, Paula</author>
   64. <title>Splish Splash</title>
   65. <genre>Romance</genre>
   66. <price>4.95</price>
   67. <publish_date>2000-11-02</publish_date>
   68. <description>A deep sea diver finds true love twenty
   69. thousand leagues beneath the sea.</description>
   70. </book>
   71. <book id="bk108">
   72. <author>Knorr, Stefan</author>
   73. <title>Creepy Crawlies</title>
   74. <genre>Horror</genre>
   75. <price>4.95</price>
   76. <publish_date>2000-12-06</publish_date>
   77. <description>An anthology of horror stories about roaches,
   78. centipedes, scorpions and other insects.</description>
   79. </book>
   80. <book id="bk109">
   81. <author>Kress, Peter</author>
   82. <title>Paradox Lost</title>
   83. <genre>Science Fiction</genre>
   84. <price>6.95</price>
   85. <publish_date>2000-11-02</publish_date>
   86. <description>After an inadvertant trip through a Heisenberg
   87. Uncertainty Device, James Salway discovers the problems
   88. of being quantum.</description>
   89. </book>
   90. <book id="bk110">
   91. <author>O'Brien, Tim</author>
   92. <title>Microsoft .NET: The Programming Bible</title>
   93. <genre>Computer</genre>
   94. <price>36.95</price>
   95. <publish_date>2000-12-09</publish_date>
   96. <description>Microsoft's .NET initiative is explored in
   97. detail in this deep programmer's reference.</description>
   98. </book>
   99. <book id="bk111">
   100. <author>O'Brien, Tim</author>
   101. <title>MSXML3: A Comprehensive Guide</title>
   102. <genre>Computer</genre>
   103. <price>36.95</price>
   104. <publish_date>2000-12-01</publish_date>
   105. <description>The Microsoft MSXML3 parser is covered in
   106. detail, with attention to XML DOM interfaces, XSLT processing,
   107. SAX and more.</description>
   108. </book>
   109. <book id="bk112">
   110. <author>Galos, Mike</author>
   111. <title>Visual Studio 7: A Comprehensive Guide</title>
   112. <genre>Computer</genre>
   113. <price>49.95</price>
   114. <publish_date>2001-04-16</publish_date>
   115. <description>Microsoft Visual Studio 7 is explored in depth,
   116. looking at how Visual Basic, Visual C++, C#, and ASP+ are
   117. integrated into a comprehensive development
   118. environment.</description>
   119. </book>
   120. </catalog>
3. Save the file with the extension “.xml”.