How To Display ASP Results Using Excel in IE with MIME Types

MORE INFORMATION

• Microsoft Windows NT Server 4.0, SP3
• Microsoft Internet Information Server (IIS), version 4.0 or later
• Microsoft SQL Server 6.5 or later
• Microsoft Excel 97, Excel 2000, and Excel 2002

Step-by-Step Procedures

1. Create a System DSN called "pubs" using the ODBC Control-Panel applet. Set up the DSN to reference your local SQL Server, use SQL Server authentication, and use "pubs" as the default database. The default login ID to the pubs database is sa, with no password.
2. Use Notepad.exe to create a file called XlTest.asp in your IIS home directory (for example, inetpub\wwwroot), and add the following to it:

   <%@ Language=VBScript %>
   <%
      'Change HTML header to specify Excel's MIME content type
      Response.Buffer = TRUE
      Response.ContentType = "application/vnd.ms-excel"
   %>
   <HTML>
   <BODY>
   Here is the info you requested.<p>
   <%
      ' Create ADO Connection object
      dim myConnection
      set myConnection = CreateObject("ADODB.Connection")
      
      ' Open SQL Server Pubs database...
      ' myConnection.Open "DSN=pubs;UID=sa"
      
      ' Get a recordset of info from Authors table...
      sqlStr = "SELECT au_fname,au_lname,phone FROM authors"
      set rsAuthors =  myConnection.Execute(sqlStr)
   %>
   
   <!-- Our table which will be translated into an Excel spreadsheet -->
   <TABLE WIDTH=75% BORDER=1 CELLSPACING=1 CELLPADDING=1>
   <TR>
      <TD><font size=+2>First Name</font></TD>
      <TD><font size=+2>Last Name</font></TD>
      <TD><font size=+2>Phone</font></TD>
   </TR>
   <!-- server-side loop adding Table entries -->
   <% do while not rsAuthors.EOF %>
   <TR>
      <TD><%=rsAuthors("au_fname")%></TD>
      <TD><%=rsAuthors("au_lname")%></TD>
      <TD><%=rsAuthors("phone")%></TD>
   </TR>
   <% rsAuthors.MoveNext
      loop
      ' Clean up
      rsAuthors.Close
      set rsAuthors = Nothing
      myConnection.Close
      set myConnection = Nothing
   %>
   </TABLE>
   </BODY>
   </HTML>
   						

3. Save XlTest.asp, and then view it from a client browser. For example, http://MyServer/XlTest.asp (replace MyServer with the name of your server.)