HOWTO: Use ASP to Query and Display Database Data in Excel

ID: Q189198

The information in this article applies to:

SUMMARY

This article describes how to build a tab delimited text file dynamically from a SQL Server or Access database query, that can be opened in Excel from within Internet Explorer using Active Server Pages. Users will be able to save the file created on their computers.


MORE INFORMATION

The following code shows how to create a tab delimited text file. The file is saved with the extension .xls so that the file will be associated with Excel, and when it is opened it will automatically invoke Excel. 


 <%@ LANGUAGE="VBSCRIPT" %>

   <HTML>
   <HEAD>
   <TITLE>Create Tab Delimited Text File</TITLE>
   </HEAD>
   <body>

         <%

         'Create a randome Filename
          nRandom = Int((1000000 - 1 + 1) * Rnd + 1000000)
          fileExcel = "t" & CStr(nRandom) & ".xls"

         'Replace 'MyWeb' with your virtual directory name or just the
         'slash if it is at the wwwroot.
          filePath= Server.mapPath("\MyWeb")
          filename=filePath & "\" & fileExcel

         'Create the File with extension .xls using the FileSytemObject
         'If the file does not exist, the TRUE parameter will allow it
         'to be created. Make sure the user* impersonated has write
         'permissions to the directory where the file is being created.

          Set fs = Server.CreateObject("Scripting.FileSystemObject")
          Set MyFile = fs.CreateTextFile(filename, True)

         'Open the connection and retrieve data from the database
          Set cn = Server.CreateObject("ADODB.Connection")

         'The following open line assumes you have set up a System
         'DataSource by the name of Pubs pointing to the Pubs database.
         'And that the sa password is blank. If you use a different
         'username and password, make sure that the user has permissions to
         'the pubs database, and select permissions on the Authors Table.
         'If you do not have SQL Server, see notes below for how to modify
         'sample to work with an Access database

          cn.Open "DSN=Pubs;UID=sa;PWD=;DATABASE=pubs"
          Set rs = cn.Execute("SELECT
   au_id,au_lName,au_fname,phone,address,city,state,zip,contract FROM
   Authors")

          strLine="" 'Initialize the variable for storing the filednames

          For each x in rs.fields
            'Separate field names with tab so that these appear in
            'different columns in Excel
            strLine= strLine & x.name & chr(9)
          Next
         'Write this string into the file
          MyFile.writeline strLine

         'Retrieve the values from the database and write into the database
          Do while Not rs.EOF
          strLine=""
          for each x in rs.Fields
            strLine= strLine & x.value & chr(9)
          next
          MyFile.writeline strLine
          rs.MoveNext
       Loop

       'Clean up
       MyFile.Close
       Set MyFile=Nothing
       Set fs=Nothing

       'Show a link to the Excel File.
       link="<A HREF=" & fileExcel & ">Open Excel</a>"
       Response.write link
          %>

   </BODY>
   </HTML>

Notes

The following describes how to modify the above sample to work on the Adventure Works Access Database that is installed as part of Internet Information Server 3.0 and 4.0.

Change the following line 

   cn.Open "DSN=Pubs;UID=sa;PWD=;DATABASE=pubs"

to this 

   cn.Open "DSN=AdvWorks"

and make sure that a System DSN exists named AdvWorks.

Change the following line 

   Set rs = cn.Execute("SELECT
   au_id,au_lName,au_fname,phone,address,city,state,zip,contract FROM
   Authors"

to this 

   Set rs = cn.Execute("SELECT * from Employees")

Notes

* In this case user refers to the user that the Web server is using to make the request. For example, if your web site is using anonymous authentication and the user profile for anonymous access is IUSR_MachineName, then IUSR_MachineName must have write permission to the directory specified in the createTextFile.


REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

http://support.microsoft.com/support/vinterdev/

Additional query words: kbASP kbDatabase kbInternet 


Keywords          : kbcode kbASP kbCOMt kbScript kbGrpASP 
Version           : winnt:
Platform          : winnt 
Issue type        : kbhowto

Last Reviewed: May 27, 1999