Katy Whitton Web Development - Codebank

Codebank - VBScript - Excel on the Fly

This code allows you to generate an Excel Spreadsheet using ASP on the fly. You can export information from arrays, database tables or forms and present them as an Excel file. You can view the table layout and information here

Click here to view the code in action

You can download the Source Database here

<% ' This Code is Copyright Katy Whitton ' You are free to use this code on any site ' But Please Keep This Copyright Statement In ' Place ' For more ASP and JavaScripts, please visit ' http://www.katywhitton.com ' Set Up our Database connection via DSNLess Connection ' You May be able to use Server.MapPath for you connection ' Or alternatively connect via ODBC, Please contact your ' Hosting provider for more information ' Dimension our Variables for the Dabase Connection dim objConn, strCon, objRS, strSQL Set objConn = Server.CreateObject("ADODB.Connection") 'Change the Line below to point to the location of the database strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\tutorials\source\sampleDB.mdb" objConn.connectionstring = strCon objConn.Open Set objRS = Server.CreateObject("ADODB.Recordset") ON ERROR RESUME NEXT ' Set the SQL Statement to get the information from the database strSQL="SELECT * FROM TBLCompany" ' Open the Database objRS.Open strSQL, objConn ' Open up the page as an Excel File Response.ContentType = "application/vnd.ms-excel" ' Set up the Table we wish to Populate %> <table border="1"> <tr> <td>ID</td> <td>CompanyName</td> <td>CompanyAddress1</td> <td>CompanyAddress2</td> <td>CompanyAddress3</td> <td>CompanyAddress4</td> <td>CompanyPostCode</td> <td>CompanyTel</td> <td>CompanyFax</td> <td>CompanyEmail</td> <td>CompanyWebsite</td> </tr> <% ' Cycle through the database and populate the table DO WHILE NOT objRS.EOF%> <tr> <td><%=objRS("ID")%></td> <td><%=objRS("CompanyName")%></td> <td><%=objRS("CompanyAddress1")%></td> <td><%=objRS("CompanyAddress2")%></td> <td><%=objRS("CompanyAddress3")%></td> <td><%=objRS("CompanyAddress4")%></td> <td><%=objRS("CompanyPostCode")%></td> <td><%=objRS("CompanyTel")%></td> <td><%=objRS("CompanyFax")%></td> <td><%=objRS("CompanyEmail")%></td> <td><%=objRS("CompanyWebsite")%></td> </tr> <% objRS.MoveNext Loop%> </table> So as you can see we can export the data quite easily into Excel from Access on the Fly and format it using standard HTML!

back

Codebank

This website and all its contents are © Katy Whitton Web Development 2005.
please view our privacy policy. This site is valid XHTML and has valid CSS