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”.

Step 3

1. Open the new file in Notepad.
   
   
2. Add the following code.
   
   1. <! DOCTYPE html>
   2. <html>
   3. <head>
   4. <title>Page Title</title>
   5. <style>
   6. h1
   7. {
   8. color: #2C9EE6;
   9. }
   10. fieldset {
   11. font-family: sans-serif;
   12. border: 5px solid #2C9EE6;
   13. background: #ddd;
   14. border-radius: 5px;
   15. padding: 15px;
   16. }
   18. fieldset legend {
   19. background: #2C9EE6;
   20. color: #fff;
   21. padding: 5px 10px ;
   22. font-size: 32px;
   23. border-radius: 5px;
   24. box-shadow: 0 0 0 5px #ddd;
   25. margin-left: 20px;
   26. }
   27. </style>
   28. <script language="vbscript" type="text/vbscript">
   30. Sub Handlechange()
   31. MsgBox "File Imported Successfully”, vbInformation
   32. End Sub
   33. Sub ExportXML
   34. Dim ObjXML
   35. Dim fileinput
   36. Dim PriceNode
   37. Dim NodeList
   38. Dim Title
   39. Dim Price
   40. Dim Nodes_Attribute
   41. Dim i
   42. Dim iRow
   43. Count=0
   44. Set ObjXML = CreateObject ("Microsoft.XMLDOM")
   45. ObjXML.async = False
   46. Set objExcel = CreateObject ("Excel. Application")
   47. Set objWorkbook =
   48. objExcel.Workbooks.Open ("C:\Users\Karthikeyan.K\Desktop\DataBook.xlsx")
   49. objExcel.Application.Visible = True
   50. objWorkbook.Windows(1).Visible = True
   51. Set XlSheet =objWorkbook.Sheets(1)
   52. XlSheet.Activate
   53. fileinput = Document.getElementById ("browse").Value
   54. ObjXML.load(fileinput)
   55. Set TitleNodes = ObjXML.SelectNodes("/catalog/book/title/text()")
   56. Set PriceNodes = ObjXML.SelectNodes("/catalog/book/price/text()")
   57. XlSheet.Range ("A1, B1, C1").Interior.ColorIndex =3
   58. XlSheet.Range ("A1, B1, C1").Borders. Value = 1
   59. XlSheet.Range ("A" & 1).Value = "Book ID"
   60. XlSheet.Range ("B" & 1).Value = "Book Titles"
   61. XlSheet.Range ("C" & 1).Value = "Price"
   62. For i = 0 To (TitleNodes.Length - 1)
   63. Title = TitleNodes(i).NodeValue
   64. Price = PriceNodes(i).NodeValue
   65. XlSheet.Range("B" & i + 2).Borders.Value = 1
   66. XlSheet.Range("C" & i + 2).Borders.Value = 1
   67. XlSheet.Range("B" & i + 2).Value = Title
   68. XlSheet.Range("C" & i + 2).Value = Price
   69. Next
   70. Set Nodes_Attribute = ObjXML.SelectNodes ("/catalog/book")
   71. For i = 0 To (Nodes_Attribute. Length - 1)
   72. Attributes = Nodes_Attribute(i).getAttribute("id")
   73. XlSheet.Range ("A" & i + 2).Borders.Value = 1
   74. XlSheet.Range ("A" & i + 2).Value = Attributes
   75. Next
   76. objWorkbook.save
   77. objWorkbook.close
   78. Set objWorkbook = Nothing
   79. Set objExcel = Nothing
   80. MsgBox "Data Read Successfully”, vbInformation
   81. End Sub
   82. </script>
   83. </head>
   85. <body>
   86. <form>
   87. <section style="margin: 10px;">
   88. <fieldset style="min-height:100px;">
   89. <legend>
   90. <b>CsharpCorner </b>
   91. </legend>
   92. <center>
   93. <h1>XML To Excel</h1>
   94. <img src="C:\Users\Karthikeyan.K\Desktop\sync-circle-blue-256.png" alt="View" style="width: 256px; height: 256px ;">
   95. <br>
   96. <br>
   97. <input type="file" id="browse" name="fileupload" onChange="Handlechange()" />
   98. <br>
   99. <br>
   100. <input type="button" value="Export" id="Export" onclick="ExportXML ()" />
   101. </center>
   102. <label>
   103. <br/>
   104. </label>
   105. <label>
   106. <br/>
   107. </label>
   108. </fieldset>
   109. </form>
   110. </body>
   112. </html>
3. Save the file with the extension “htm”.

Output

1. Open the HTML document.
   
   
   Figure 3: XML to Excel
   
   
2. Click the Browse button then choose the XML document.
   
   
   Figure 4: Choose the XML document
   
   
   Figure 5: File Imported successfully
   
   
3. Press the Export button.
   
   
   Figure 6: Data read successfully
   
   
4. Open the Excel workbook to check the spreadsheet.
   
   
   Figure 7: Final result

Conclusion

Thanks for reading. I hope you liked this article. Please provide your valuable suggestions.