Microsoft KB Archive/189198

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

Article ID: 189198

Article Last Modified on 5/2/2006

-

APPLIES TO


 * Microsoft Active Server Pages 4.0

-



This article was previously published under Q189198



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" %>

  Create Tab Delimited Text File 

<%

'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= and 'PWD= 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= ;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="Open Excel" Response.write link %>

 