...

View Full Version : Extract data from EXCEl using ASP



larman
12-16-2003, 01:59 PM
Below is my asp code:
<%

Set Conn = Server.CreateObject("ADODB.Connection")
str = "Driver={Microsoft Excel Driver (*.xls)}; DBQ=" & Server.MapPath("/chart/test.xls")
Conn.Open str

Set rs = Server.CreateObject("ADODB.Recordset")
SQL ="SELECT * FROM testsheet"
rs.Open SQL, Conn
rs.movefirst
do while not rs.eof
response.Write(rs("test")&" | "&rs("test1")& " & "<br>")
rs.movenext
Loop
rs.close
set rs=nothing
conn.close
set conn=nothing
%>
The error is:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] Microsoft Jet can't find the testsheet object...
Can somebody tell me where am i wrong?
Or somebody can show me an easy example about using ASP to extract data and display Excel data?
The attachted pic is the test.xls

Roelf
12-16-2003, 02:37 PM
when using excel as a database, you have to define a named range in the sheet, which ado will treat as a table

select your cells, select the insertmenu, click name, click define, enter "testsheet", save the sheet.

now it should work

larman
12-17-2003, 12:23 AM
Hi,Roelf,
Can you explain more in detail?
In face,testsheet is a worksheet in test.xls,so my sql is
"select * from testsheet",is this not correctly?

larman
12-17-2003, 01:23 AM
hmm...i have work it out,but i still have a question.
Does it must select the range of cell,insert the range,define the range,name the range,and add the range?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum