Export to Excel multiple worksheets

popup · January 31st, 2012, 06:31 AM

Hi There,

This might be a very old school query. But I am not able to crack it yet after hours of googling also.

"How do I export data into multiple worksheets from classic ASP page"

Any help on this regard is highly appreciable. Following is the piece of code how I am exporting data into a single worksheet -

<%
response.charset = "UTF-8"
Response.Buffer=true
'Response.ContentType = "application/download"
Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader "Content-Disposition", "attachment; filename=" & "File01.xls"
%>
<HTML xmlns:x="urn:schemas-microsoft-com:office:excel">
<HEAD>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>File01</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>File01-Actions</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>File01-Detailed</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
</HEAD>
<BODY>
<TABLE>
<%

' Connection String
dim SQL, oConn

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open vMdbConnStr

Dim rs, ds

Set rs = Server.CreateObject("ADODB.Recordset")
SQL = request.querystring("DBQuery")
rs.Open SQL, oConn
ds = rs.GetRows
Dim i, j
dim c

Response.Write "<tr>"
For c = 0 to UBound(ds,1)
Response.Write "<td style='border-left:none;border:.5pt solid windowtext;' bgcolor='#CCCCCC' bordercolor='#000000'><b>"
Response.Write rs.Fields(c).Name
Response.Write "</b></td>"
Next
Response.Write "</tr>" & vbcrlf

rs.Close
Set rs = Nothing
oConn.Close
Set oConn = Nothing

' Showing Each Row
For i = 0 To UBound(ds, 2)
Response.Write "<tr>"
' Showing Each Column
For j = 0 To UBound(ds, 1)
Response.Write "<td>"
Response.Write ds(j, i)
Response.Write "</td>"
Next
Response.Write "</tr>" & vbcrlf
Next

Erase ds

%>
</TABLE>
</BODY>
</HTML>

Thanks!

mat41 · January 31st, 2012, 06:29 PM

Please make sure you post your solution if you find one. I have tried to achieve this with pure ASP before without success. Im not so sure its possible to be honest. Search this forum for more info, there have been a number of posts over the years. Good luck!