Export to Excel

[Matt]

I have an ASP page that calls ASP routines that I created that execute
a database query and return the results to a recordset. I then iterate
through the recordset and display the data in a table. Before I iterate
through the recordset I instruct the browser that the content type is
Excel using the following line:

(Response.ContentType = "application/vnd.ms-excel")

This works fine with Excel 2003 but with older versions (I tested Excel
97) the HTML included within the include files (on Page One) is
displayed in an Excel worksheet.

Page One - Begins with Include files (which contain my subroutines and
databse connection information). Then I have a Select statement to
determine which export users want. When I hit the case I need I then
call a Sub routine that exports the data to Excel.

Page Two - this page contains all of my custom functions and
procedures.

Page One Example Code:
<!-- #INCLUDE FILE="../../../includes/common_settings.asp" -->
<!-- #INCLUDE FILE="../includes/composite_settings.asp" -->
<!-- #INCLUDE FILE="../includes/composite_functions.asp" -->

<%
sSymbol = Request("Symbol")
sExportType = Request("ExportType")

'Determine Selected Export Type
Select Case lcase(sExportType)
' '
'QUARTERLY EXPORT
Case "quarter"
' '
'Execute the Display Quarter Procedure
call DisplayQuarterExport(sSymbol)
' '
'ANNUAL EXPORT
Case "annual"
' '
'Execute the Display Annual Procedure
call DisplayAnnualExport(sSymbol)
' '
'RAW EXPORT
Case "raw"
' '
'Execute the Display Annual Procedure
call DisplayRawExport(sSymbol)
' '
End Select
%>

Page Two Procedure:
<%
Sub DisplayQuarterExport(pSymbol)
'Execute Quarter Function
set pRSQuarter = QuarterExportQuery(pSymbol)
' '
'Tell the Browser to redirect the output to Excel
Response.ContentType = "application/vnd.ms-excel"
' '
'Check is Recordset contains data
If pRSQuarter.RecordCount > 0 then
' '
'Move to the first record in the recordset
pRSQuarter.MoveFirst
' '
'Display the title bar
Response.Write "<table border=1>"
Response.Write "<tr>"
' '
'Iterate through the fields collection
for each field in pRSQuarter.Fields
Response.Write "<td>"
Response.Write "<b>" & replace(field.name,"_"," ") & "</b>"
Response.Write "</td>"
next
' '
Response.Write "</tr>"
' '
'Move to the first record in the recordset
pRSQuarter.MoveFirst
' '
'Display data
Do until pRSQuarter.EOF
Response.Write "<tr>"
' '
'Composite Code
Response.Write "<td>"
Response.Write trim(pRSQuarter.Fields("Composite").Value)
Response.Write "</td>"
'Composite Date
Response.Write "<td>"
Response.Write pRSQuarter.Fields("Composite_Date").Value
Response.Write "</td>"
'Gross Return
Response.Write "<td>"
Response.Write Round(pRSQuarter.Fields("Gross").Value,2) & "%"
Response.Write "</td>"
'Gross UV
Response.Write "<td>"
Response.Write Round(pRSQuarter.Fields("Gross_UV").Value,2)
Response.Write "</td>"
'Net Return
Response.Write "<td>"
Response.Write Round(pRSQuarter.Fields("Net").Value,2) & "%"
Response.Write "</td>"
'Net UV
Response.Write "<td>"
Response.Write Round(pRSQuarter.Fields("Net_UV").Value,2)
Response.Write "</td>"
'Total Equity
Response.Write "<td>"
Response.Write pRSQuarter.Fields("Total_Equity").Value
Response.Write "</td>"
'Total Fixed
Response.Write "<td>"
Response.Write pRSQuarter.Fields("Total_Fixed").Value
Response.Write "</td>"
'Cash
Response.Write "<td>"
Response.Write pRSQuarter.Fields("Cash").Value
Response.Write "</td>"
' '
Response.Write "</tr>"
pRSQuarter.MoveNext
Loop
' '
'Close Quarter Export Recordset
CloseRS(pRSQuarter)
' '
'Close Database Connection
CloseDBConnection()
' '
Response.Write "</table>"
' '
'Display Error Message is no results were found
Else
Response.Write "Your Query returned (0) results."
' '
End If
' '
End Sub
%>

Again this works great if I have Excel 2003 but for older versions of
Excel it displays the HTML that is contained within the include files
on page one.

Here is my assumption but I have no clue why Excel 2003 works. I am
assuming that the include file content is written to the screen (behind
the scenes) and when I instruct the browser that the content is Excel,
the include file data is included in the "content" and is displayed in
Excel.

Please help, I am racking my head against the wall with this one.

 

[Matt]

I figured out a solution.

Before I set the content type (Response.ContentType) I clear the buffer
by executing the following line.

Response.Clear

This cleared the existing content from the buffer.