Using an Excel sheet as a Datasource via ODBC

Putting data from an Excel sheet into a web page

There is often important company information kept and maintained in Excel sheets. The problem is, how to get the latest version of frequently changed data into html pages, once a company starts to move to presenting internal information on their intranet. It makes it much easier for staff if there is no change required in the current update routines.

Fortunately, there is an easy way to use excel sheets as a datasource using ODBC. First of all, check to be sure that the Microsoft Excel ODBC drivers are installed on the web server.

Go to the control panel
Double click on the ODBC icon.
You should see the Excel drivers under the Drivers tab.

If they aren't there, you may need to try reinstalling ODBC and check to be sure the Excel drivers are selected.

Next you need to be sure that the data you want to access is defined in a table.

Open the Excel file and highlight the cells required.
From the menu bar, select Insert | Name | Define and enter the name you want to use for this table.
Make sure that the first row contains the names you want to use for each of the fields.

Once the data required can be found in named tables, you can run SQL statements to find the specific data you want to display.

The table below was generated from the Excel sheet above by using SQL via ODBC in an ASP page. The ASP vbscript code used is listed following the table.

#	date	weekday	subject	count
1	1998-03-01	tue	cars	27
2	1998-03-02	wed	food	53
3	1998-03-03	thu	beer	1047
4	1998-03-04	fri	cards	13
5	1998-03-05	sat	camels	44
6	1998-03-06	sun	xyz	123
7	1998-03-07	mon	abc	321

<html>
<head>
<title>Using an Excel sheet as a Datasource via ODBC</title>
<style>
th {background:black;color:white}
.value {background:white;color:black}
.count {background:silver;color:black}
</style>
</head>
</html>
<body bgcolor=white >
<%
' Change the following variables to reflect
' the names and locations for your file and
' table(s)
PATH="c:\inetpub\wwwroot\ASP\"
FILE="datatable.xls"
TABLE="ClassTable"
' Change this SQL statement as required
SQL="SELECT * FROM " & TABLE
DRIVER="{Microsoft Excel Driver (*.xls)}"
DB="DBQ=" & PATH & FILE & ";"
DB= DB & "DefaultDir=" & PATH & ";"
DB= DB & "Driver=" & DRIVER & ";"
DB = DB & "FIL=excel 5.0;ReadOnly=1;"
Set DataConn=Server.CreateObject("ADODB.Connection")
DataConn.ConnectionTimeout = 15
DataConn.CommandTimeout = 30
DataConn.Open DB
Set RS_item = DataConn.Execute(SQL)
response.write "<table border cellspacing=0><tr>"
if RS_item.EOF then
    response.write "    <td colspan=10>No Records found</td>"
else
    response.write "<th valign=bottom>#</th>"
    for each field in RS_item.fields
	    response.write"<th valign=bottom >" & field.name & "</th>"
	next
end if
response.write "</tr>"
count=0
do while not RS_item.EOF
   count=count+1
   response.write " <tr><td class=value align=left valign=top bgcolor=silver>" & count & "</td>"
   for each field in RS_item.fields
		response.write "<td class=value align=left valign=top>" & field.value & "</td>"
   next
   response.write "</tr>"
   RS_item.MoveNext
Loop
DataConn.Close
DataConn = null
%>
</table>
</body>
</html>

This page is based on a letter in Robert Hess's 'Geek to Geek' column in the May 1998 issue of MIND - volume 3 number 5.

There is a related MSDN article on how to send an html table from the server, and have it open in excel on the client side. Check it out here. An update to that article is here

comments, corrections and questions welcome to :
housten@hotmail.com or view guestbook or sign guestbook

my Tripod homepage browser printing via script(OLD Browsers)