On Sun, 4 Sep 2005 22:01:16 -0500, "glen" <none@n...> wrote:

>I used the below asp code to transform xml to a spreadsheet using a .xsl 
>file.
>It works great; problem is I want to transform several recordset to xml to
>multiple worksheets in the workbook.  Should I use something other than the
>"transformNodeToObject"?  I can't see how to direct the xml to different
>worksheets.  I appreciate any help.

It's probably the wrong approach. Just set the worksheet part of your
XSL stylesheet to loop multiple times, so you'd have 

<Worksheet>
	<xsl:param name="SheetNumber" select="1" />

<!-- Pass in the sheet number as a parameter, or call this template
recursively until you've processed all the worksheet data -->

	<xsl:attribute name="ss:Name"><xsl:text>Sheet<xsl:value-of
select="$SheetNumber" /></xsl:text>

<!-- other parts of the worksheet - rows, cols etc -->

</Worksheet>

Also, I'd change the order of operation, and instead, pass in a
parameter before processing the xslt and use the parameter to set the
expanded row count value - let the stylesheet set the attribute itself
rather than post-processing it.

HTH
Cheers - Neil


>
><%@ Language="vbscript"%>
><%
>    sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
>Server.MapPath("mydb.mdb")
>    sXSL = Server.MapPath("mydb.xsl")
>    Response.Buffer = True
>    'Retrieve an ADO recordset of the Orders Detail table in mydb
>    Dim rs, nRecords
>    Set rs = CreateObject("ADODB.Recordset")
>    rs.Open "SELECT uDesc FROM mydbTable", 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 stylesheet
>    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
>
>%>
>
>