I am use following coding for the Excel file connection with ASP.
It give following error in connection string.
I check Connection string "test Connection successful " . But when I use this string in the asp file it give following error message.

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/101/xl_data.asp, line 43, column 20
cnnExcel.Open "DBQ="D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls;Defa ultDir=D:\DCIL\DOWNLOAD\101SAMPLES;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\matdsn2.dsn;MaxScanRows=8;PageTimeout=5;Re adOnly=0;SafeTransactions=0;Threads=3;UID=admin;Us erCommitSync=Yes;"
-------------------^


ASP file connection string coding
----------------------------------
Const adOpenStatic = 3
Const adLockPessimistic = 2

Dim cnnExcel
Dim rstExcel
Dim I
Dim iCols

Set cnnExcel = Server.CreateObject("ADODB.Connection")

cnnExcel.Open "DBQ="D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls;Defa ultDir=D:\DCIL\DOWNLOAD\101SAMPLES;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\matdsn2.dsn;MaxScanRows=8;PageTimeout=5;Re adOnly=0;SafeTransactions=0;Threads=3;UID=admin;Us erCommitSync=Yes;"



Mateen

There are a couple of problems, especially with nested quotes, however I'd advise switching to oledb rather than use odbc. Under this link click the Excel icon and oledb for an easier and better connection string:

http://www.connectionstrings.com/

Joe

--

Joe

First problem (as per joefawcett):

"DBQ="D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls
-----^ (extra ")

Second problem: Is there a path:
D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls
on the server? When you test a connection, it is tested locally. When you publish, the paths must be relative to the server.

Recommendation: Publish xl_data.xls to your root web folder then use Server.MapPath() as:

cnnExcel.Open "Driver={Driver do Microsoft Excel(*.xls)};" & "DBQ=" & Server.MapPath("xl_data.xls") & ";"

I am working on localhost.
I use following coding. but now it give this error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.
/mtp/myxl_data.asp, line 44


asp coding
----------
<%
Response.ContentType = "application/vnd.ms-excel"
Const adOpenStatic = 3
Const adLockPessimistic = 2

Dim cnnExcel
Dim rstExcel
Dim I
Dim iCols

Set cnnExcel = Server.CreateObject("ADODB.Connection")
cnnExcel.Open "Driver={Driver do Microsoft Excel(*.xls)};" & "DBQ=" & Server.MapPath("xl_data.xls") & ";"

Set rstExcel = Server.CreateObject("ADODB.Recordset")
rstExcel.Open "SELECT * FROM TestData;", cnnExcel, _
    adOpenStatic, adLockPessimistic // line 44
%>


what is the problem ?
how I can use excel file in ASP page ?

Please help

Mateen

Do you have a named range in your Excel file called "TestData"?
Are adOpenStatic and adLockPessimistic defined?

adOpenStatic and adLockPessimistic (and adCmdText) are defined in a file called adovbs.inc. If you can't find the file then use these:

Const adCmdText = 1
Const adOpenStatic = 3
Const adLockPessimistic = 2

rstExcel.Open "SELECT * FROM TestData;", cnnExcel, _
    adOpenStatic, adLockPessimistic, adCmdText

thanks for your response.
now it give this error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] External table is not in the expected format.
/mtp/xl_data3.asp, line 32

I could not understand
"Do you have a named range in your Excel file called "TestData"? "

how I can do named range in excel file ?
in the excel file how can defined range of data ?

I am using following coding.
--------------------------------
Const adCmdText = 1
Const adOpenStatic = 3
Const adLockPessimistic = 2

Dim cnnExcel
Dim rstExcel
Dim I
Dim iCols

Set cnnExcel = Server.CreateObject("ADODB.Connection")
cnnExcel.Open "DBQ=" & Server.MapPath("xl_data.xls") & ";" & "DRIVER={Microsoft Excel Driver (*.xls)};"


Set rstExcel = Server.CreateObject("ADODB.Recordset")

rstExcel.Open "SELECT * FROM testdata;", cnnExcel, adOpenStatic, adLockPessimistic

iCols = rstExcel.Fields.Count
%>
<table border="1">
    <thead>
        <%

        For I = 0 To iCols - 1
            Response.Write "<th>"
            Response.Write rstExcel.Fields.Item(I).Name
            Response.Write "</th>" & vbCrLf
        Next 'I
        %>
    </thead>
    <%
    rstExcel.MoveFirst

    Do While Not rstExcel.EOF
        Response.Write "<tr>" & vbCrLf
        For I = 0 To iCols - 1
            Response.Write "<td>"
            Response.Write rstExcel.Fields.Item(I).Value
            Response.Write "</td>" & vbCrLf
        Next 'I
        Response.Write "</tr>" & vbCrLf

        rstExcel.MoveNext
    Loop
    %>
</table>

<%
rstExcel.Close
Set rstExcel = Nothing

cnnExcel.Close
Set cnnExcel = Nothing
%>



Mateen

When you say "SELECT * FROM TestData;" it means: get the data from the range (named range) called "TestData".

Select your cells which contain your data (your range). Then select menu Insert|Name|Define and called it TestData.

I just created an Excel file with some data and a named range called TestData and tested your code. It works. You just have to create the named range in your Excel spreadsheet before the select query can retrieve the data.

thanks for your response.
I am trying but it is not retrieve the data from the Excel file.
Error is same

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] External table is not in the expected format.
/mtp/xl_data3.asp, line 32

any alternate suggestion (help) ?

regards.

Mateen

Hai,

Im new to this forum,
i need u guys help in something.
Im very new to ASP...very very new and im given a task to query excel
from ASP. I found an example on
http://support.microsoft.com/default...NoWebContent=1

but im having some problem with it.
I've done all the steps but im getting the error 'ODBC driver does not support the requested properties'

plz do help...thanks in advance...