Import SQL query results into Excel

<%
Databasename= "Db.mdb"
Apath=server.mappath (".") & "/xmllover.xls"

Set Conn=server.createobject ("Adodb.connection")
Constr= "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & Server.MapPath (DatabaseName)
Conn.Open Constr
Set Rs=server.createobject ("Adodb.recordset")
' Orderly
Sql= "SELECT * FROM Employees ORDER BY EmployeeID ASC"
' Reverse order
' sql= ' SELECT * FROM Employees ORDER BY EmployeeID DESC "
Rs.Open Sql,conn
Msg= "No." & Chr (9) & "Name" & vbCrLf
Do as not rs.eof
Msg=msg & RS ("EmployeeID") & Chr (9) & RS ("LastName") & vbCrLf
Rs.movenext
Loop
Set F=server.createobject ("Scripting.FileSystemObject")
Set Myfile=f.createtextfile (apath,true)
MyFile.WriteLine msg
Myfile.close
Rs.close
Set rs=nothing
%>

Another way is to directly output the file in CSV format, and then use response. For output,

Sub CSV ()
Response.Buffer = True
Response.AddHeader "Content-disposition", "attachment"; Filename=member "&formatdatetime" (date,2) & ". csv"
Response.Charset = "Utf-8"
Response.ContentType = "Application/octet-stream"
' Add table column name output code here
Response.Flush ()
' Add the data output code here
Response.Flush ()
End Sub

Let's take a look at an ASP tutorial instance that generates an Excel document

<%dim excelstring
Dim excelstr
Dim excelstr2
Dim head
excelstring = ""
Head = "Sn" & "," & "Reason" & "," & "Empid" & "," & "EmpName" & "," & "Ext" & "," &am P "Depname"
Excel= "Creat"
If excel<> "" Then
Set rs2 = Server.CreateObject ("ADODB. Recordset ")
SQL2 = "SELECT * FROM Value_table"
Rs2.open sql2,conn,1,3
Do as not rs2.eof
Excelstr = "" & Rs2 (0) & "" "" "" & Rs2 (1) & "" "," "& Rs2 (2) &" "", "" & Rs2 (3) & "" "," "" & Rs2 (4) & "" "," "" "& Rs2 (5) &" "" "
EXCELSTR2 = excelstr2 & Chr (a) & Excelstr
Rs2.movenext
Loop
excelstring = Head &AMP;CHR & EXCELSTR2
' Response. Write (excelstring)
' Rs2.close
Set rs2=nothing
Set fs = CreateObject ("Scripting.") FileSystemObject ")
Set exc = fs. OpenTextFile (server. MapPath ("Excel.csv"), 2,true)
Exc.write (excelstring)
If Fs. FileExists (Server.MapPath ("Excel.csv")) Then
Response.Write ("<a href=excel.csv> report has been generated, click View </a>")
' Response.Write (excelstring)
Else
Response.Write ("Generate Report failed!") ")
End If
End If%>