by Scud   Monday, August 14, 2006 Rating:

Printable View  | Email Story  | Write Comment

Aim of this articles is to display records from an Microsoft Excel in an ASP. We could use general SQL command while retreving data from an excel sheet. All we need to know how to connect Excel file.

Here is the excel database connection string :

Connection String for Excel

strConnection = "DBQ=" & Server.MapPath("customer-list.xls") & "; DRIVER={Microsoft Excel Driver (*.xls)};"

We will use a specific driver to connect to Excel files. ODBC plays a major role in coding.

An Example :


strConnection = "DBQ=" & Server.MapPath("customer-list.xls") & "; DRIVER={Microsoft Excel Driver (*.xls)};"

Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
cn.open strConnection


sql="select * from sales_in_2005;"

rs.Open sql, cn, adOpenStatic, adLockPessimistic


do while not rs.eof
    response.write rs("customerName") & " : " & rs("soldPrice") & "<br/>"
rs.movenext
loop


rs.close
Set rs = nothing
cn.close
Set cn = nothing


In this example, we have used an example Excel files that holds some data containing customerName and soldPrice cells. We have displayed all cells with sample data on our page.

Happy Coding

• ¤ Methods of Session Object
• ¤ Simple XML/RSS Parser - Part II
• ¤ Selecting random record from database
• ¤ Uploading Images to Database Using Persist AspUpload
• ¤ Simple XML/RSS Parser