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

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.
         'You must change User UID=<username> and 
         'PWD=<strong password> to the correct values before you run
         'this code. Make sure that UID has the appropriate permissions to 
         'perform this operation on the 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=<username>;PWD=<strong password>;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=<username>;PWD=<strong password>;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.