How to use ASP & Connect Excel Using ADO (DRIVER={Microsoft Excel Driver (*.xls)})

Author: admin | Posted: 24-04-2010 | Category: ASP & Excel (Excel.Application)

How to use ASP & Connect Excel Using ADO (DRIVER={Microsoft Excel Driver (*.xls)}) This is learn/tutorial asp developers how to using ASP script Connect Excel ADO Driver.

ShotDev Focus:
- ASP & Connect Excel ADO Driver.

Example

asp_excel_ado.asp

view plain copy to clipboard print ?

<%Option Explicit%>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<%
Dim OpenFile,i
Dim Conn,objRec,strSQL
OpenFile = "MyXls/MyExcelDB.xls"
Set Conn = Server.CreateObject("ADODB.Connection")
'*** Connect to Excel ***'
Conn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath(OpenFile) & "; "
'*** Select Sheet ***'
strSQL = "SELECT * FROM [Sheet1$]"
Set objRec = Conn.Execute(strSQL)
%>
<table width="420" border="1">
<tr>
<td>CustomerID</td>
<td>Name</td>
<td>Email</td>
<td>CountryCode</td>
<td>Budget</td>
<td>Used</td>
</tr>
<%
While Not objRec.EOF
%>
<tr>
<td><%=objRec.Fields("CustomerID").Value%></td>
<td><%=objRec.Fields("Name").Value%></td>
<td><%=objRec.Fields("Email").Value%></td>
<td><%=objRec.Fields("CountryCode").Value%></td>
<td><%=objRec.Fields("Budget").Value%></td>
<td><%=objRec.Fields("Used").Value%></td>
</tr>
<%
objRec.MoveNext
Wend
%>
</table>
<%
objRec.Close
Conn.Close
Set objRec=Nothing
Set Conn=Nothing
%>
</body>
</html>

<%Option Explicit%>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<%
Dim OpenFile,i
Dim Conn,objRec,strSQL

OpenFile = "MyXls/MyExcelDB.xls"
Set Conn = Server.CreateObject("ADODB.Connection")

'*** Connect to Excel ***'
Conn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath(OpenFile) & "; "

'*** Select Sheet ***'
strSQL = "SELECT * FROM [Sheet1$]"
Set objRec = Conn.Execute(strSQL)
%>
<table width="420" border="1">
<tr>
<td>CustomerID</td>
<td>Name</td>
<td>Email</td>
<td>CountryCode</td>
<td>Budget</td>
<td>Used</td>
</tr>
<%
While Not objRec.EOF
%>
<tr>
<td><%=objRec.Fields("CustomerID").Value%></td> <!-- objRec.Fields(0).Value or objRec.Fields("CustomerID").Value -->
<td><%=objRec.Fields("Name").Value%></td>
<td><%=objRec.Fields("Email").Value%></td>
<td><%=objRec.Fields("CountryCode").Value%></td>
<td><%=objRec.Fields("Budget").Value%></td>
<td><%=objRec.Fields("Used").Value%></td>
</tr>
<%
objRec.MoveNext
Wend
%>
</table>
<%
objRec.Close
Conn.Close
Set objRec=Nothing
Set Conn=Nothing
%>
</body>
</html>

Create a asp file and save to path root-path/myasp/

Run
http://localhost/myasp/asp_excel_ado.asp

Screenshot

.
.
.
Download this script.