ASP to Excel SpreadsheetS

evergrean100 · Dec 23, 2006

I can create an Excel spreadsheet output with data from my Access 2003 database but now need to create an Excel output with two spreadsheets in one Excel file.

My attempt below is not working. Please advise if this can be done??

Code:

<%
dim accessdb, cn, rs, sql, sql2
' Connect to the db with a DSN connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "dsn=actis"

' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

sql = "SELECT id, " & _
     "[lastname], " & _
     "[firstname], " & _
     "FROM mytable"
rs.Open sql, conn


sql2 = "SELECT idTwo, " & _
     "[lastnameTwo], " & _
     "[firstnameTwo], " & _
     "FROM mytableTwo"
rs.Open sql2, conn
%>

<%
Response.ContentType = "application/vnd.ms-excel"
	Response.AddHeader "Content-Disposition", "attachment;filename=ExcelExport.xls"
%>
<html>
<body>
<table BORDER="1" borderColor="gainsboro" align="center">
<tr>
<td>Claim ID</td>
<td>Last Name</td>
<td>First Name</td>
</tr>
' first spreadsheet
<%
' Move to the first record
rs.movefirst

' Start a loop that will end with the last record
do while not rs.eof
%>

<tr>
<td>
<%= rs("combo_id") %>
</td>

<td>
<%= rs("firstname") %>
</td>

<td>
<%= rs("lastname") %>
</td>



</tr>

<%
' Move to the next record
rs.movenext
' Loop back to the do statement
loop %>
</table>



<table BORDER="1" borderColor="gainsboro" align="center">
<tr>
<td>Claim ID</td>
<td>Last Name</td>
<td>First Name</td>
</tr>
'second spread sheet
<%
' Move to the first record
rs.movefirst

' Start a loop that will end with the last record
do while not rs.eof
%>

<tr>
<td>
<%= rs("combo_idTwo") %>
</td>

<td>
<%= rs("firstnameTwo") %>
</td>

<td>
<%= rs("lastnameTwo") %>
</td>



</tr>

<%
' Move to the next record
rs.movenext
' Loop back to the do statement
loop %>
</table>




</body>
</html>

<%
' Close and set the recordset to nothing
rs.close
set rs=nothing

Excel can have multiple spreadsheets but I can only get my ASP to output just one Excel with one spreadsheet. Anyone ever output with two spreadsheets??

onpnt · Dec 23, 2006

You'll have to create a Workbook object and then multiple Worksheet objects to reference under that.

Take a look at this for some help on the code

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

evergrean100 · Dec 23, 2006

Thanks,

I tried this and it just gives me a blank web page in my IIS web root.

Please advise what I am missing?

Code:

<%@ Language=VBScript %>

<html>
<body>
hwew

<%
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"

Dim objWorkbook, c, objSheet1, objSheet2, objSheet3, intCol, intRow

Set objWorkbook = CreateObject("OWC10.Spreadsheet")
Set c = objWorkbook.Constants
Set objSheet1 = objWorkbook.Worksheets(1)
objSheet1.Name = "One"
Set objSheet2 = objWorkbook.Worksheets(2)
objSheet2.Name = "Two"
objWorkbook.Worksheets(3).Delete
 
objSheet1.Activate
Call WriteData(3, "Red")

objSheet2.Activate
Call WriteData(7, "Blue")

objWorkbook.DisplayToolbar = False
objWorkbook.AutoFit = True
objSheet1.Activate

Response.Write objWorkbook.XMLData
Response.End
   
Set objWorkbook = Nothing
Set c = Nothing
Set objSheet1 = Nothing
Set objSheet2 = Nothing
Set objSheet3 = Nothing

Sub WriteData(pintDivisor, pstrColor)
   With objWorkbook.ActiveSheet
      For intRow = 1 To 100
         For intCol = 1 To 10
            .Cells(intRow, intCol).Value = (intRow - intCol) / pintDivisor
            If .Cells(intRow, intCol).Value Mod 3 = 0 Then
               .Cells(intRow, intCol).Interior.Color = pstrColor
            End If
         Next
         .Cells(intRow, 11).Value = "= I" & CStr(intRow) & "+J" & CStr(intRow)
         If intRow Mod 2 = 0 Then .Cells(intRow, 11).Interior.Color = "LightGray"
      Next
      .Columns("A:D").AutoFilter
   End With
End Sub
%>

</body>
</html>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ASP to Excel SpreadsheetS

evergrean100

Technical User

onpnt

Programmer

evergrean100

Technical User

Similar threads

Part and Inventory Search

Sponsor