Connecting to an Excel Spreadsheet in ASP

You can connect using Active Server Pages to an Excel spreadsheet in much the same way that you connect to an Access database.  Here is what you need to change based on the way we have been connecting to a database in Access:

In your Excel spreadsheet:

1. Put the "field" names in separate columns and the data for the various records immediately in the rows below.  For example, put "first" in A1, "last" in "B1", "state" in C1.  Then "Bill" in A2, "Pegram" in B2, "VA" in C2, etc.  You do not use quotes for any of these entries.
2. Select the rows and columns where your data resides, including the "field names".  Then choose Insert>Name>Define and give this range a name.
3. If you need to put lots of text in a cell, highlight the cell and choose Format>Cell>Alignment>Wrap Text so that you can see of the text.  You can apply this same procedure to multiple cells at one time just by highlighting multiple cells and then doing the formatting.

In the ASP file:

1. In the Open() statement to connect to a database, replace "Microsoft Access Driver (*.mdb)" with "Microsoft Excel Driver (*.xls)"  and then for Server.MapPath put the path to the Excel spreadsheet instead of the path to the Access database.
2. In the Execute() statement (for example rsItem = connCW.Execute("SELECT * from students") replace the name of the table (here students) with the name of the named range you created in Excel.

This information is based in part on Microsoft Knowledge Base Article Q195951

Revised: September 28, 2002.  Comments to William Pegram, wpegram@nvcc.edu