April 10th, 2007, 11:36 AM
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 3
Time spent in forums: 2 h 55 sec
Reputation Power: 0
|
|
|
Exporting Data to Excel (another method)
I found a really easy way to export my data to an Excel file from Access or SQL. I found this information on a Microsoft support page. I'm simply posting it as a courtesy to those in need.
First, I created a new file (createfile.asp) with the following code in it:
Code:
<%@ 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,z ip,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>
Then, over on my page where you can view the data, I added a link to export the data to Excel:
Code:
<%
'Show a link to the Excel File.
link="<A HREF=" & fileExcel & ">Export to Excel</a>"
Response.write link
%>
If you have questions, please search the Microsoft site for "HOWTO: Use ASP to Query and Display Database Data in Excel". 
|
ASP Free Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
