Transfering asp to excel

Garabaldi · Mar 18, 2002

Hi,

I have a page set up in asp which pulls certain fields in a database.

I need to have that page exported into excel.

I know there is a way to do this through excel through the Grab Data method and an .iqy file.

However, whenever I try to do this i end up getting an error.

Does anyone know a way to get this to work?

Thanks!!!

Tarwn · Mar 21, 2002

A simple way to do it would be to create a .csv file and just write your information to it as comma-delimited text.

Each row is on a seperate line, all values are comma seperated
file is readable by excel and also (bonus) half a million other programs

rickpill · Mar 22, 2002

Try this, I got it from Microsoft.

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

http://wwwroot.

filePath= Server.mapPath("\MyWeb&quot

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&quot

Set MyFile = fs.CreateTextFile(filename, True)

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

'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&quot

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>

.....Rick

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Transfering asp to excel

Garabaldi

Technical User

Tarwn

Programmer

rickpill

Programmer

Similar threads

Part and Inventory Search

Sponsor