ID: Q195951
The information in this article applies to:
This article demonstrates how to query and update information in an Excel spreadsheet using ActiveX Data Objects (ADO) from an Active Server Page. The article also discusses the limitations associated with this type of application.
IMPORTANT: Though ASP/ADO applications support multi-user access, an Excel spreadsheet does not. Therefore, this method of querying and updating information does not support multi-user concurrent access.
To access the data in your Excel spreadsheet for this sample, use the Microsoft ODBC Driver for Excel. Create a table to access the data by creating a Named Range in your Excel spreadsheet.
1. Create the Excel file ADOtest.xls with the following data in sheet1:
column1 column2 column3
---------------------------
rr this 15
bb test 20
ee works 25
a. Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
The request properties can not be supported by this ODBC Driver.
b. Microsoft OLE DB Provider for ODBC Drivers error '80004005'
The query is not updateable because it contains no searchable
columns to use as a hopeful key.
c. Microsoft OLE DB Provider for ODBC Drivers error '80004005'
Query based update failed. The row to update could not be found.
a. Highlight the row(s) and column(s) area where your data resides.
b. On the Insert menu, point to Name, and click Define.
c. Enter the name myRange1 for the Named Range name.
d. Click OK.
The Named Range myRange1 contains the following data:
column1 column2 column3
-----------------------------
rr this 15
bb test 20
ee works 25
NOTE: ADO assumes that the first row in an Excel query contains the
column headings. Therefore, the Named Range must include the column
headings. This is different behavior from DAO.
NOTE: Column headings cannot be a number. The Excel driver cannot
interpret them and, instead, returns a cell reference. For example, a
column heading of "F1" would be misinterpreted.
ADOTest.xls file.
a. From the Control Panel, open the ODBC Administrator.
b. On the System DSN tab, click Add.
c. Select Microsoft Excel Driver (*.xls) and click Finish. If this
option does not exist, you need to install the Microsoft ODBC
driver for Excel from Excel setup.
d. Choose ADOExcel for the Data Source Name.
e. Make sure the Version is set to Excel 97.
f. Click "Select Workbook...", browse to the ADOTest.xls file, and
click OK.
g. Click the "Options>>" button and clear the "Read Only" check box.
Click OK and then click OK again.
If your Active Server Page is accessed anonymously, you need to make sure that the Anonymous Account (IUSR_<MachineName>) has at least Read/Write (RW) access to the spreadsheet. If you want to delete information from the spreadsheet, you need to grant the permissions accordingly.
If you are authenticating access to your Active Server Page, you need to ensure that all users accessing your application have the appropriate permissions.
NOTE: If you do not set the appropriate permissions on the spreadsheet, you get an error message similar to the following:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine
cannot open the file '(unknown)'. It is already opened exclusively by
another user, or you need permission to view its data.
<!-- Begin ASP Source Code -->
<%@ LANGUAGE="VBSCRIPT" %>
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "ADOExcel"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn
objRS.CursorType = 3 'Static cursor.
objRS.LockType = 2 'Pessimistic Lock.
objRS.Source = "Select * from myRange1"
objRS.Open
%>
<br>
<%
Response.Write("Original Data")
'Printing out original spreadsheet headings and values.
'Note that the first recordset does not have a "value" property
'just a "name" property. This will spit out the column headings.
Response.Write("<TABLE><TR>")
For X = 0 To objRS.Fields.Count - 1
Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
Next
Response.Write("</TR>")
objRS.MoveFirst
While Not objRS.EOF
Response.Write("<TR>")
For X = 0 To objRS.Fields.Count - 1
Response.write("<TD>" & objRS.Fields.Item(X).Value)
Next
objRS.MoveNext
Response.Write("</TR>")
Wend
Response.Write("</TABLE>")
'The update is made here
objRS.MoveFirst
objRS.Fields(0).Value = "change"
objRS.Fields(1).Value = "look"
objRS.Fields(2).Value = "30"
objRS.Update
'Printing out spreadsheet headings and values after update.
Response.Write("<br>Data after the update")
Response.Write("<TABLE><TR>")
For X = 0 To objRS.Fields.Count - 1
Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
Next
Response.Write("</TR>")
objRS.MoveFirst
While Not objRS.EOF
Response.Write("<TR>")
For X = 0 To objRS.Fields.Count - 1
Response.write("<TD>" & objRS.Fields.Item(X).Value)
Next
objRS.MoveNext
Response.Write("</TR>")
Wend
Response.Write("</TABLE>")
'ADO Object clean up.
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
<!-- End ASP Source Code -->
will see the following:
Original Data:
column1 column2 column3
-----------------------------
rr this 30
bb test 20
tt wow 25
Data after the update:
column1 column2 column3
-----------------------------
change look 30
bb test 20
tt wow 25
For additional information, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q190195
TITLE : HOWTO: ExtractInformation From Excel Sheet with DAO
Keywords : kbADO kbASP kbGrpASP
Version : WINDOWS:1.0,1.5,2.0; WINNT:
Platform : WINDOWS winnt
Issue type : kbhowtoLast Reviewed: November 21, 1998