asked on

Export data to Excel from Classic ASP page

Hi Experts,

The query below returns data to my Classic ASP page from SQL Server database:

SELECT * FROM Complaints WHERE DateRcvd between '" & Request.Form("Date1") & "' AND '" & Request.Form("Date2") & "'

I would like to export the returned data from my web page to Excel by placing an "Export to Excel" text link on the page.
When users click the link an Excel sheets should open and get populated with the data that is already diplayed on the web page.
Is there a way this could be done by using JavaScript or any other method? I would appreciate your help.

jmdl1983

The following will build your SQL command & execute it. Then set the necessary HTTP headers and MIME type then output the file to it. The browser would see the info, and receive it as a normal file. When opened in Excel, it may prompt that the contents are not standard, but it will allow you to open it as a normal excel file.

 'Build & Execute Your SQL Command
 strSQL = "SELECT * FROM Complaints WHERE DateRcvd between '" & Request.Form("Date1") & "' AND '" & Request.Form("Date2") & "'"
 Set rstSearch = cnnSearch.Execute(strSQL)

 'Set the HTTP Header
 Response.ContentType = "application/vnd.ms-excel"
 Response.AddHeader "Content-Disposition", "filename=""Excel Export.xls"""

 'Output Table %>
 <table border="1" cellspacing="0" cellpadding="5">
  <tr><th>Header 1</th><th>Header 2</th><th>Header 3</th><th>Header 4</th><th>Header 5</th></tr><% Do While Not rstSearch.EOF %>
  <tr><td><%=rstSearch(0)%></td><td><%=rstSearch(1)%></td><td><%=rstSearch(2)%></td><td><%=rstSearch(3)%></td><td><%=rstSearch(4)%></td></tr><% rstSearch.MoveNext : Loop %>
 </table>

 <%
 'Stop Processing Page Now
 Response.End

Open in new window

Stefan Motz

ASKER

Thank you for your quick response. Where do I have to place this code? Is this a separate page which is linked from my ASP page by the "Export to Excel" text link?

ASKER CERTIFIED SOLUTION

jmdl1983

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Stefan Motz

ASKER

Thank you very much; it's perfect!
Exactly what I needed.

jmdl1983

Glad you got it worked out. Thanks for the points!