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!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Output directly to Excel..? 2

Status
Not open for further replies.
kenjoswe

kenjoswe

Technical User
Sep 19, 2000
327
SE
Hi all,

I have a asp-page that displays the results from a executed SQL-query. This works fine.

Set Conn=Server.CreateObject("ADODB.Connection")
conn.Open
set rs=Server.CreateObject("ADODB.Recordset")
sql="sql statement"
rs.open sql,conn,3,3

But how should I do if want to send the recordset directly to Excel instead?

/Kent J.
 
Sort by date Sort by votes
The best way would be to use the filesystem object & send out a csv file:

Set FSO = Server.CreateObject("Scripting.FileSystemObject")
Call Response.AddHeader("Content-Disposition","attachment; filename=""Incident.csv""")
Response.ContentType = "bad/type"
For Each Field In RS.Fields
response.write Field.name & ","
Next
Response.Write vbcrlf

Do While Not RS.EOF
For Each Field In RS.Fields
If IsNull(Field) Then
Response.Write ","
Else
Response.Write Field.Value & ","
End If
Next
Response.Write vbcrlf
RS.MoveNext
Loop

Will output all of the fields in the query into a csv file saved on the users computer.

HTH

Ben ----------------------------------------
Ben O'Hara
----------------------------------------
 
Upvote 0 Downvote
oharab,

I've tried that with:
if Request.Form("flag")="EXCEL" then
'.....your code

end if

But what happends is that I'm promted to open the actual asp-page instead of the data in the recordset..?

Why is that?

/Kent J.

 
Upvote 0 Downvote
oharab,

I've checked again and this is what happends:

1.) I'm asked to Open or Save "Incident.csv"
2.) If I choose Open then I'm promted again to open "Incident.csv
3.) If I then open the file Excel starts.
But the recordset is stored in only in one string in Excel.

How can I separate the different records?

/Kent J.
 
Upvote 0 Downvote
This is the code I use - key point is to take out of your page any html tags like <html> <body> as then Excel thinks it's an html file not a csv file.
This isn't my code - I got it from - Got Data? Send It to Your Users as an Excel Spreadsheet from Your ASP Page By Rama Ramachandran
Regards
Alasdair

Code: 
<%
' -- Tell the browser the data is a Comma Separated Values List (CSV)
response.ContentType=&quot;application/csv&quot;
' -- Tell the browser to associate a file name with the data
Response.AddHeader &quot;Content-Disposition&quot;, &quot;filename=mydata.csv;&quot;

set cnADO = Server.CreateObject(&quot;ADODB.Connection&quot;)
cnADO.Open &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\Webs\Development Intranet\departments\pharmacy\jmarquess\Interventions.mdb&quot;

set myrs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
myrs.Open &quot;ajsOutPut&quot;, cnADO, 1, 3

' -- First, send the header row containing the field names.
' -- For this example we are using the recordset field names. 
' -- You could also substitute with your own header field names if required.
' -- Assume that your recordset variable is called objRS

Dim i, strQuote
For i = 0 to myrs.Fields.Count - 1
	Response.Write myrs.Fields(i).Name & &quot;,&quot;
Next
' -- End the line so your row is complete; use the built-in constant 'vbnewLine'
Response.Write vbNewLine

' -- Then send the entire data set

Do While Not myRS.EOF
   For i = 0 to myRS.Fields.Count - 1
   	Response.Write myRS.Fields(i) & &quot;,&quot;
   Next
   ' -- End the line so your row is complete
   Response.Write vbNewLine
   ' -- Next record
   myRS.MoveNext
Loop
myrs.close
cnado.close
%>
 
Upvote 0 Downvote
mackers2,

Thanks! It works great!

Only one problem.
My recordset has comma in company name field.

Example: Volvo, Svenska AB

Is it possible to use Tab instead but still get Excel to open automatically?

/Kent J.

 
Upvote 0 Downvote
Kenjoswe, I think you may be better off to output your recordset in an EXCEL file rather than csv file. You won't have problem with the comma.

All you need is this line

<%response.contenttype=&quot;application/vnd.ms-excel&quot;%>

And output the recordset in a html table tags as usual.


 
Upvote 0 Downvote
TechnicalAnalysis,

OK! Thanks!
Now I have the recordset in Excel and with the right format.

But Excel is integrated in the Browserwindow and doesn't display all functionality in Excel.

How can I open the recordset in (ordinary) Excel the same way as a .CSV file?

/Kent J.
 
Upvote 0 Downvote
this works for my test data with commas in, of course you'll have problems with multiple &quot; in your fields then... if you find out how to get Excel to open it instead of inside the browser I'd love to know.
Code: 
	Response.Write &quot;&quot;&quot;&quot; & myRS.Fields(i) & &quot;&quot;&quot;&quot; & &quot;,&quot;
I prefer to use the csv file as you don't make your users use Excel.
Alasdair
 
Upvote 0 Downvote
Instead of using &quot;,&quot; between fields, try using a tab:

Response.Write Field.Value & chr$(9)

this will give you a tab seperated file which should import into excel no problem.

HTH

Ben
----------------------------------------
Ben O'Hara
----------------------------------------
 
Upvote 0 Downvote
oharab,
Thanks! Now it works really nice.

/Kent J.


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
245
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
194
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
189
leifoet
leifoet
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
497
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to link a condition to a session(&quot;UID&quot;) ?
Replies
14
Views
308
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top