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

Article translations Article translations
Article ID: 285891 - View products that this article applies to.
This article was previously published under Q285891
Expand all | Collapse all

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.

MORE INFORMATION

Create the XML template for the workbook

  1. Create a new folder, C:\ExcelXML.
  2. 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)
  3. In cell F3, type the following formula:
    =SUM(F$2:F2)
  4. 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.
  5. 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.
  6. Select column E. On the Format menu, click Cells. On the Number tab, click Percentage and specify 0 decimal places, and then click OK.
  7. Select column F. On the Format menu, click Cells. On the Number tab, click Accounting and then click OK.
  8. 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.
  9. On the Tools menu, click Options. On the View tab, clear the Gridlines check box and click OK.
  10. Select row 2. On the Window menu, click Freeze Panes.
  11. Select cell A1.
  12. On the File menu, click Save As. Browse to the C:\ExcelXML folder you created, and save the workbook as "Orders.xsl" 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.
  13. Quit Excel.
  14. Open Orders.xsl in any text editor, such as Notepad.
  15. Insert the following between the <?xml version="1.0"?> tag and the <Workbook> tag:
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
    <xsl:template match="/">
    <xsl:pi name="xml">version="1.0"</xsl:pi>
    					
  16. At the very end of Orders.xsl, append the following two lines:
    </xsl:template>
    </xsl:stylesheet>
    					
  17. Locate the following set of tags in Orders.xsl (this set of tags represents cells A2:F2 in the worksheet)
       <Row ss:Height="14.25">
        <Cell><Data ss:Type="String">aaa</Data></Cell>
        <Cell><Data ss:Type="Number">111</Data></Cell>
        <Cell><Data ss:Type="Number">222</Data></Cell>
        <Cell><Data ss:Type="Number">333</Data></Cell>
        <Cell><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:Formula="=RC[-3]*RC[-2]*1*(1-RC[-1])"><Data ss:Type="Number">73926</Data></Cell>
       </Row>
    					
    and replace it with the following XSL code:
    <xsl:for-each select="xml/rs:data/z:row">
       <Row ss:AutoFitHeight="0" ss:Height="13.5">
        <Cell><Data ss:Type="String"><xsl:value-of select="@OrderID"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@ProductID"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@UnitPrice"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@Quantity"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@Discount"/></Data></Cell>
        <Cell ss:Formula="=RC[-3]*RC[-2]*(1-RC[-1])"><Data ss:Type="Number">0</Data></Cell>
       </Row>
    </xsl:for-each>
    					
  18. Save your changes to Orders.xsl, and close the file.

Use Visual Basic to transform an XML-persisted ADO recordset into an XML spreadsheet

  1. In Visual Basic, create a new Standard EXE project.
  2. On the Project menu, click References. Select the type libraries for Microsoft ActiveX Data Object 2.5 (or later) and Microsoft XML 3.0.
  3. Add a CommandButton to Form1, and add the following code to the button's Click event:
        Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                      "C:\program files\microsoft office\office10\samples\Northwind.mdb"
        Const sOutXML = "C:\ExcelXML\Orders.xml"
        Const sXSL = "C:\ExcelXML\Orders.xsl"
        
        '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 "SELECT * FROM [Order Details]", 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 "Parse Error: " & oResults.parseError.reason
        Else
           'Modify the ss:ExpandedRowCount attribute for the <table> 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("Workbook/Worksheet/Table")
            oTable.setAttribute "ss:ExpandedRowCount", 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("Excel.Application")
            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.
  4. Press F5 to run the program.
  5. 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.
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("Excel.Application")
        Set oBook = oExcel.Workbooks.Add
        oBook.Worksheets(1).Range("B2").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

  1. 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="vbscript"%>
    
    <%
    
        Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files\microsoft office\office10\samples\Northwind.mdb"
        Const sXSL = "C:\ExcelXML\Orders.xsl"
        
        Response.Buffer = True
    
        'Retrieve an ADO recordset of the Orders Detail table in Northwind.
        Dim rs, nRecords
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open "SELECT * FROM [Order Details]", sConn, 3, 3
            
        'Persist the recordset to a new DOMDocument and store the record count.
        Dim oXML
        Set oXML = CreateObject("Microsoft.XMLDOM")
        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("Microsoft.XMLDOM")
        oXSL.Load sXSL
        
        'Transform the XML using the style sheet.
        Dim oResults
        Set oResults = CreateObject("Microsoft.XMLDOM")
        oXML.transformNodeToObject oXSL, oResults
        
        If oXSL.parseError.errorCode <> 0 Then
           Response.Write "Parse Error: " & oResults.parseError.reason
        Else
    
           'Modify the ss:ExpandedRowCount attribute for the <table> node in the XSL.
            Dim oTable
            Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
            oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2
    
            'Return the resulting XML Spreadsheet for display in Excel.
            Response.ContentType = "application/vnd.ms-excel"
            Response.Charset = "ISO-8859-1"
            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.

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

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:
Collapse this imageExpand this image
Download
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.

REFERENCES

For more information about saving ADO recordsets in XML format, see the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms681538.aspx
For more information on streaming MIME content to Microsoft Office applications, click the following article numbers to view the articles in the Microsoft Knowledge Base:
266263 BUG: Word 2000 and Excel 2000 display ASP source when using MIME type to stream data
199841 How to display ASP results using Excel in IE with MIME types
271572 How to format an Excel workbook while streaming MIME content
307021 How to transfer XML data to Microsoft Excel 2002 by using Visual Basic .NET

Properties

Article ID: 285891 - Last Review: March 19, 2007 - Revision: 7.4
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Active Server Pages 4.0
  • Microsoft Visual Basic 6.0 Professional Edition
Keywords: 
kbdownload kbhowto KB285891

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com