Make your own free website on

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.

<title>Using an Excel sheet as a Datasource via ODBC</title>
th {background:black;color:white}
.value {background:white;color:black}
.count {background:silver;color:black}
<body bgcolor=white >
' Change the following variables to reflect
' the names and locations for your file and
' table(s)
' Change this SQL statement as required
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>"
    response.write "<th valign=bottom>#</th>"
    for each field in RS_item.fields
	    response.write"<th valign=bottom >" & & "</th>"
end if
response.write "</tr>"
do while not RS_item.EOF
   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>"
   response.write "</tr>"
DataConn = null
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 : or view guestbook or sign guestbook

my Tripod homepage     browser printing via script(OLD Browsers)