importing data from MS Access to Excel using ASP

craigger1 · #1 (**permalink**) 08-04-03, 01:20 PM

Here is the scenario...

Using ASP code, I want to set up the following on a web page...

a link that when clicked on asks you if you want to download a MS Excel file. however, whenever this link is clicked on by a user, it first feeds the MS Excel file with records from an MS Access database

if anyone can help or has any questions, please post a reply or e-mail me at craig_madrin@goodyear.com

thank you

sequill · #2 (**permalink**) 08-05-03, 04:11 PM

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)