Transform XML into HTML with an XSL Stylesheet Programmatically
Earlier in this chapter, we created an XSL stylesheet for the Courses.xml document. Now that you are familiar with the XML DOM object model, you can use the TransformNode method of the DOMDocument object to apply this XSL to your XML file. The procedure you need to write will have two DOMDocument objects, one with the XML file (Courses.xml) and one with the XSL file (Courses.xsl). To see the results of XML to HTML transformation, save the generated HTML to a string variable and print it to the Immediate window. Next, write the variable's content to a text file (refer to Chapter 8 for details on creating text files). Finally, open the newly created HTML file in Excel 2002.
The following exercise demonstrates how to transform XML into HTML programmatically.
1. Open the Courses.xml file in Notepad.
2. Locate the line that links the XML file to a stylesheet and comment it out as follows:
<!-- <?xml-stylesheet type="text/xsl" href="Courses2.xsl"?> -->
Because the example TransformXML procedure will apply a stylesheet programmatically to this XML document, you can get rid of this line or simply comment it out as shown.
3. Save and close the Courses.xml file.
4. In the Visual Basic Editor window, enter the Transform XML procedure shown below. Be sure to modify the file paths to point to a valid location of the required documents on your hard disk.
Sub TransformXML()
Dim xmldoc As MSXML2.D0MDocument30 Dim xsldoc As MSXML2.D0MDocument30 Dim fs As Object Dim myFile As Object Dim strHTML As String Dim strFile As String
' Load XML document Set xmldoc = New MSXML2.D0MDocument30 xmldoc.Load "C:\JK_B00KS_ALL\Excel_EN_2002\" _ & "ChaptersNew\Chapter_17\Courses.xml"
' Load XSL stylesheet Set xsldoc = New MSXML2.D0MDocument30 xsldoc.Load "C:\JK_B00KS_ALL\Excel_EN_2002\" _ & "ChaptersNew\Chapter_17\MyCourses.xsl"
' apply the stylesheet to transform XML to HTML ' and write out the HTML to a string strHTML = xmldoc.transformNode(xsldoc) Debug.Print strHTML
' save the string to HTML file strFile = "C:\NewCourses.htm"
Set fs = Create0bject("Scripting.FileSystem0bject") Set myFile = fs.CreateTextFile(strFile, True) myFile.Write strHTML myFile.Close
' 0pen the file in Excel Workbooks.0pen strFile End Sub
After you run the TransformXML procedure shown above, both the Immediate window and the C:\NewCourses.htm file contain the following HTML code:
<HTML xmlns:xsl="http://www.w3.org/TR/WD-xsl"> <HEAD>
<TITLE>VBA Course Schedule</TITLE> </HEAD>
<B0DY bgcolor="yellow"> <CENTER>
<STR0NG>VBA Course Schedule</STR0NG><P />
<TABLE border="1" cellPadding="4" cellSpacing="2"> <TR>
<TH>Course Id</TH> <TH>Course Title</TH> <TH>Start Date</TH> <TH>No of Sessions</TH> </TR> <TR>
<TD>Advanced VBA in Excel</TD>
<TD>Beginning VBA in Excel</TD>
<TD>Intermediate VBA in Excel</TD>
<FONT face="Tahoma">
Using Sample XSL stylesheet 'Courses.xsl' prepared by 'put your name here' </FONT></H6> </BODY> </HTML>
Post a comment