Microsoft KB Archive/285891

= How to use Visual Basic or ASP to create an XML spreadsheet for Excel 2002 and Excel 2003 =

Article ID: 285891

Article Last Modified on 3/19/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Active Server Pages 4.0
 * Microsoft Visual Basic 6.0 Professional Edition

-



This article was previously published under Q285891



SUMMARY
Microsoft Excel 2002 and Microsoft Office Excel 2003 support an XML format with which they can both load and save workbooks (or XML spreadsheets). By using this XML spreadsheet format, you can create multi-sheet, formatted workbooks to Excel without using Automation. This approach may be desirable when you need to create an Excel workbook, but it is not feasible to automate Excel (such as on a Web server or from a service) or Excel is not installed on the system where your code is run.

This article illustrates how you can create an XML template that, when used with Extensible Stylesheet Language (XSL) transformation, generates a formatted workbook that can be opened directly in Excel. The XML transformation is demonstrated for both Active Server Pages (ASP) and Visual Basic. With regard to the Visual Basic code sample, the spreadsheet data is generated solely with XML/XSL; minimal Automation is used to open the results in Excel.

Note The sample described in this article is available for download; for download instructions, see the Download section at the end of this article.



Create the XML template for the workbook
 Create a new folder, C:\ExcelXML.  In Excel, start a new workbook. Add the following data to cells A1:F2 of the workbook in the cells indicated. Cell F2 should be entered as a formula. A1: Order ID   B1: Product ID   C1: Unit Price   D1: Quantity   E1: Discount  F1: Total A2: aaa        B2: 111          C2: 222          D2: 333        E2: 0         F2: =C2*D2*(1-E2)  In cell F3, type the following formula:

=SUM(F$2:F2)

 Select cells A1:F1. On the Format menu, click Cells. Apply a bold font, a bottom border, and a solid color for cell shading. Click OK. Select columns A:F. On the Format menu, point to Column and then click Width. Type 15 for the new column width and then click OK. With columns A:F still selected, on the Format menu, click Cells. On the Alignment tab, select Center from the horizontal alignment list, and then click OK. Select column E. On the Format menu, click Cells. On the Number tab, click Percentage and specify 0 decimal places, and then click OK.</li> Select column F. On the Format menu, click Cells. On the Number tab, click Accounting and then click OK.</li> Select cells A3:F3. On the Format menu, click Row Height, type 25, and then click OK. With A3:F3 still selected, on the Format menu, click Cells. Apply a top border to the cells, and then click OK.</li> On the Tools menu, click Options. On the View tab, clear the Gridlines check box and click OK.</li> Select row 2. On the Window menu, click Freeze Panes.</li> Select cell A1.</li> On the File menu, click Save As. Browse to the C:\ExcelXML folder you created, and save the workbook as &quot;Orders.xsl&quot; in the XML Spreadsheet format.

Note: In the file name box in the Save As dialog box, enclose the file name in double quotes so that an .xml extension is not added to your file name.</li> Quit Excel.</li> Open Orders.xsl in any text editor, such as Notepad.</li>  Insert the following between the <?xml version=&quot;1.0&quot;?> tag and the <Workbook> tag: <xsl:stylesheet xmlns:xsl=&quot;http://www.w3.org/TR/WD-xsl&quot;> <xsl:template match=&quot;/&quot;> <xsl:pi name=&quot;xml&quot;>version=&quot;1.0&quot;</xsl:pi> </li>  At the very end of Orders.xsl, append the following two lines: </xsl:template> </xsl:stylesheet> </li>  Locate the following set of tags in Orders.xsl (this set of tags represents cells A2:F2 in the worksheet) <Row ss:Height=&quot;14.25&quot;> <Cell> </Cell> <Cell> </Cell> <Cell> </Cell> <Cell> </Cell> <Cell> </Cell> <Cell ss:Formula=&quot;=RC[-3]*RC[-2]*1*(1-RC[-1])&quot;> </Cell> </Row> and replace it with the following XSL code: <xsl:for-each select=&quot;xml/rs:data/z:row&quot;> <Row ss:AutoFitHeight=&quot;0&quot; ss:Height=&quot;13.5&quot;> <Cell> </Cell> <Cell> </Cell> <Cell> </Cell> <Cell> </Cell> <Cell> </Cell> <Cell ss:Formula=&quot;=RC[-3]*RC[-2]*(1-RC[-1])&quot;> </Cell> </Row> </xsl:for-each> </li> Save your changes to Orders.xsl, and close the file.</li></ol>

Use Visual Basic to transform an XML-persisted ADO recordset into an XML spreadsheet
<ol> In Visual Basic, create a new Standard EXE project.</li> On the Project menu, click References. Select the type libraries for Microsoft ActiveX Data Object 2.5 (or later) and Microsoft XML 3.0.</li>  Add a CommandButton to Form1, and add the following code to the button's Click event: Const sConn = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & _ &quot;C:\program files\microsoft office\office10\samples\Northwind.mdb&quot; Const sOutXML = &quot;C:\ExcelXML\Orders.xml&quot; Const sXSL = &quot;C:\ExcelXML\Orders.xsl&quot; 'Retrieve an ADO recordset of the Orders Detail table in Northwind. Dim nRecords As Long, nFields As Long Dim rs As New ADODB.Recordset rs.Open &quot;SELECT * FROM [Order Details]&quot;, sConn, adOpenStatic, adLockOptimistic 'Persist the recordset to a new DOMDocument and store the record count. Dim oXML As New DOMDocument rs.Save oXML, adPersistXML nRecords = rs.RecordCount nFields = rs.Fields.Count rs.Close 'Load the XSL (the workbook template with XSL directives) into a DOMDocument. Dim oXSL As New DOMDocument oXSL.Load sXSL 'Transform the XML using the style sheet. Dim oResults As New DOMDocument oXML.transformNodeToObject oXSL, oResults If oXSL.parseError.errorCode <> 0 Then MsgBox &quot;Parse Error: &quot; & oResults.parseError.reason Else 'Modify the ss:ExpandedRowCount attribute for the node to      'indicate the correct number of rows (count of records + 1 row for       'the header + 1 row for the total). Dim oTable As MSXML2.IXMLDOMElement Set oTable = oResults.selectSingleNode(&quot;Workbook/Worksheet/Table&quot;) oTable.setAttribute &quot;ss:ExpandedRowCount&quot;, nRecords + 2

'***************************************       'Save the results to a file. Open sOutXML For Output As #1 Print #1, oResults.xml Close #1

'Open the XML in Excel. Dim oExcel As Object Set oExcel = CreateObject(&quot;Excel.Application&quot;) oExcel.Workbooks.Open sOutXML oExcel.Visible = True oExcel.UserControl = True '***************************************   End If Note If needed, modify the connection string (Const sConn) so that it contains the correct installation path to the sample Access Northwind database. </li> Press F5 to run the program.</li> <li>Click the button on Form1. The data from the Orders Detail table in the Northwind Database appears in the Excel workbook template that you created.</li></ol>

The sample generates the XML Spreadsheet and Automates Excel using the Open method of the Workbooks collection to open the XML as a new workbook. As an alternative approach, you can directly insert the XML into an existing worksheet at a specific cell. To illustrate, replace the code that is contained between the asterisks with the following: 'Display the data in a workbook starting at cell B2. Dim oExcel As Object, oBook As Object Set oExcel = CreateObject(&quot;Excel.Application&quot;) Set oBook = oExcel.Workbooks.Add oBook.Worksheets(1).Range(&quot;B2&quot;).Resize(nRecords + 2, nFields + 1).Value(11) = _ oResults.xml 'Note: xlRangeValueXMLSpreadsheet=11 oExcel.Visible = True oExcel.UserControl = True After you make this change, run the program again. Note that this time, the XML Spreadsheet data is inserted into a new workbook starting at cell B2 of the first worksheet. Cell data and formats are applied to the range; however, using this approach, settings specific to the rows, columns, worksheet, and workbook are not carried over.

Use ASP to transform an XML-persisted ADO recordset into an XML spreadsheet
<ol> <li> Paste the following code into Notepad. Save the code as ExcelXML.asp in the virtual root folder of your Web server.Note The default virtual root folder is C:\inetpub\wwwroot. <%@ Language=&quot;vbscript&quot;%>

<%

Const sConn = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files\microsoft office\office10\samples\Northwind.mdb&quot; Const sXSL = &quot;C:\ExcelXML\Orders.xsl&quot; Response.Buffer = True

'Retrieve an ADO recordset of the Orders Detail table in Northwind. Dim rs, nRecords Set rs = CreateObject(&quot;ADODB.Recordset&quot;) rs.Open &quot;SELECT * FROM [Order Details]&quot;, sConn, 3, 3 'Persist the recordset to a new DOMDocument and store the record count. Dim oXML Set oXML = CreateObject(&quot;Microsoft.XMLDOM&quot;) rs.Save oXML, 1 nRecords = rs.RecordCount rs.Close 'Load the XSL (the workbook template with XSL directives) into a DOMDocument. Dim oXSL Set oXSL = CreateObject(&quot;Microsoft.XMLDOM&quot;) oXSL.Load sXSL 'Transform the XML using the style sheet. Dim oResults Set oResults = CreateObject(&quot;Microsoft.XMLDOM&quot;) oXML.transformNodeToObject oXSL, oResults If oXSL.parseError.errorCode <> 0 Then Response.Write &quot;Parse Error: &quot; & oResults.parseError.reason Else

'Modify the ss:ExpandedRowCount attribute for the node in the XSL. Dim oTable Set oTable = oResults.selectSingleNode(&quot;Workbook/Worksheet/Table&quot;) oTable.setAttribute &quot;ss:ExpandedRowCount&quot;, nRecords + 2

'Return the resulting XML Spreadsheet for display in Excel. Response.ContentType = &quot;application/vnd.ms-excel&quot; Response.Charset = &quot;ISO-8859-1&quot; Response.Write oResults.XML Response.Flush

End If

%> Note If needed, modify the connection string (Const sConn) so that it contains the correct installation path to the sample Access Northwind database.

</li> <li>Start Internet Explorer and browse to http://YourWebServer/ExcelXML.asp, where YourWebServer is the name of your Web server. The data from the Orders Detail table in the Northwind Database appears in the Excel workbook template that you created.</li></ol>

Additional notes
The following Excel features cannot be persisted with the XML Spreadsheet format:
 * Charts
 * OLE Objects
 * Drawing shapes or AutoShapes
 * VBA Projects
 * Group and Outline

Download
XMLSpread.exe contains the XML stylesheet, Visual Basic project, and ASP script described in this article. The following file is available for download from the Microsoft Download Center:

Download the Xmlsprd.exe package now. Release Date: March 27, 2001

For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to obtain Microsoft support files from online services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.

<div class="references_section">