This was easier than I though it would be:
Short Answer:
If you rename a .html to a .xls, Excel will open it as an excel doc. So all you have to do is output the db information in html to a file with a .xls extension.
Longer answer:
see short answer then continue here:
I put an example on my site with a handy-dandy zip file with all the pages.
http://www.sequill.net/hotscripts/102
All the work is done in the generateExcell.asp (sp?) file. It's just an ASP file the redirects at the end.
[code]
<%
' if you've got a large DB, you might want to increase
' the server timeout here
'set up the database/ recordset
Dim strProvider, strSQL, rsLinks
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("accessDB.mdb")
strSQL = "SELECT * FROM siteList ORDER BY hits "
set rsLinks = Server.CreateObject("ADODB.Recordset")
rsLinks.Open strSQL, strProvider, 1, 3, adCmdTable
'setup the excel file
Dim objFSO, objExcelFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcelFile = objFSO.CreateTextFile(Server.MapPath("siteList.xls"))
'Do the header information
objExcelFile.writeline ("<html>")
objExcelFile.writeline ("<table border=1>")
'heading
objExcelFile.writeline ("<tr >")
objExcelFile.writeline (" <td bgcolor=#cccccc> Title</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> Url</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> Hits</td>")
objExcelFile.writeline ("</tr>")
'loop through the recordsets and fill in the cells
Dim intRowCount ' used for calculation at the end
intRowCount = 1
if not (rsLinks.eof and rsLinks.bof) then
rslinks.movefirst
while not rsLinks.eof
objExcelFile.writeline ("<tr bgcolor=#ffffff>")
objExcelFile.writeline (" <td>" & rsLinks("Title") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("url") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("Hits") & "</td>")
objExcelFile.writeline ("</tr>")
intRowCount = intRowCount +1
rslinks.movenext
wend
end if
'do a calculation
objExcelFile.writeline ("<tr>")
objExcelFile.writeline (" <td bgcolor=#cccccc> </td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> <b>Total:</b></td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> =sum(c1:c"& intRowCount & ")</td>")
objExcelFile.writeline ("</tr>")
'close up shop
objExcelFile.writeline ("</table>")
objExcelFile.writeline ("</html>")
'redirect to the download link.
response.redirect ("download.asp")
%>
[code]
Now all I need to do is figure out how to do a cool " Generating File" page the redirects to the download page when finished..(pref. without javascript)