Using Excel Spreadsheets As Web Data Sources

Problem

You want to create a way to easily manage a small set of data which can be easily maintained by the average office worker.

Pre-requisites

1. Microsoft Internet Information Server 4.0
2. Microsoft Excel ODBC Driver installed on Web Server.
3. Microsoft ActiveX Data Objects version 2.0 or greater installed on Web Server.
4. Microsoft Excel 95, 97 or 2000

Solution

Create an Excel Spreadsheet that contains the data. Allow the user to update this via FTP, or have a scheduled process that does this for them. Then create an Active Server Pages script which uses ActiveX Data Objects to gather the information from the spreadsheet and display it.

Example

To illustrate this solution, we'll create an online event listing. We want this to be updated by the events coordinator, who is not very web saavy.

Create the Excel Spreadsheet

To begin, create a new Worksheet in Microsoft Excel. In the first row, place the names you would like for your fields. In this example, we need to know the name of the event, the location of the event, who is coordinating it and the date and time of the event.

Now, we need to fill in some data.

Now, highlight the the columns that contain your data.

From the menu, Select Insert, Name, Define. In the dialog box that appears, enter the name for the highlighted data. In this example, we'll call it "Events".

Hit OK. You should see the name of your data group in the "Name Box" at the upper left hand corner of the screen.

Now your data should be all set. If your worksheet is protected or is placed in a secure directory, make sure that the user that the web server is running as (usually IUSR_MachineName) has the appropriate permissions. Save your data file and place it on your web server.

Create the ASP code

The first thing that we have to do is create the Connection and Recordset that we will be using.

'## Create Connection and Recordsets
    Dim Conn, RS
    Set Conn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

Next, lets create a variable for the file name. In this example, let's say we named the file "EventData.xls".

'## The Excel File Name
    XLFile = "F:\webdata\EventData.xls"

Now, we have to define what data we want to get. Most of the time, we'll want all the data. This case is no exception, so we create a SQL query to get everything from the data we defined as "Events".

'## The Data to extract
    szSQL = "select * from Events"

To get the data, we need to open the connection to the data source. Here is where we stick in the name of the file defined above. You could just as easily put the file in this line, as well.

'## Create and open the connection
    Conn.ConnectionString = "DBQ=" & XLFile & ";DRIVER=Microsoft Excel Driver (*.xls);UID=admin;"
    Conn.Open

Once this is done, we set some properties on the recordset and use the Recordset.Open method to get the specific data from our spreadsheet. By default, ADO creates Forward Only recordsets. We'll make ours static. After we get the data, we can disconnect the Active connection, since we don't need it anymore.

'## set the cursor to be static.
    rs.cursortype = 3 ' adStatic.
'## open the recordset
    rs.open szSQL, conn
'## Disconnect recordset, eliminate connection
    rs.activeconnection = nothing
    Conn.Close
    Set Conn=nothing

Now we can operate on the data just like any other recorddata. Here, we will iterate through the data, displaying the events on the web. Naturally, we could generate any formatting we want.

'## iterate through the recordset.
while not rs.eof
  response.write RS("name") & " -- "
  response.write RS("location")  & " -- "
  response.write RS("Coordinator")  & " -- "
  response.write RS("Date")  & " -- "
  response.write RS("Time")  & "<BR>"
  rs.movenext
wend

Finally, we close out the recordset and release it. Of couse, you could skip this set and move the data into the user's session, if they were going to be querying it repeatedly. For now, we'll assume that isn't the case.

RS.close
Set RS=nothing

This should get you started. Of course there are all kinds of neat things you can do once your data is imported into an ADO Recordset. Excellent information is available from the Able Consulting's Technology Page.

References

For more information, see the Microsoft Knowledge Base Article: HOWTO: Query and Update Excel Data Using ADO From ASP.

This article originally posted at Algonquin Studios.