ASP and Excel

[Thies Meier]

Hello,

in German ASP-Newsgroups nobody had an idea,
so I would like to ask the following here:

You can simply read the content of an Excel
worksheet via ASP by executing a sql-statement
like SELECT * FROM [Table1$A:E]

That works fine, but how can I search for one
special row for example ? I have tried
SELECT * FROM [Table1$A:E] WHERE [Table1$D] Like '" & searchstring & "%';
to search a string in column D, but it hadn't worked.
Anybody an idea ?

Maybe it is a question of the connection drivers ?
Should I use the Jet or the Excel drivers ? How
the connection-string would look like for the
sample above ?

Thanks for any help
Greetings
Thies

 

[Bob Barrows]

Hello,

in German ASP-Newsgroups nobody had an idea,
so I would like to ask the following here:

You can simply read the content of an Excel
worksheet via ASP by executing a sql-statement
like SELECT * FROM [Table1$A:E]

That works fine, but how can I search for one
special row for example ? I have tried
SELECT * FROM [Table1$A:E] WHERE [Table1$D] Like '" & searchstring &
"%'; to search a string in column D, but it hadn't worked.
Anybody an idea ?

Maybe it is a question of the connection drivers ?
Should I use the Jet or the Excel drivers ? How
the connection-string would look like for the
sample above ?

Thanks for any help
Greetings
Thies

I think you're going to have to use the recordset's Find or Filter method
(probably the latter) to accomplish this. Excel is not a database engine so
I would not expect it to support sql queries.

Bob Barrows

 

[Mike]

When I did this years ago I would name the table range then it thinks the
top row of the field is the column name. So
Select * from <mynamedrange> where <myfirstrow> Like '" & searchstring &
"%';

of course don't use Select * in production code.

HTH
Mike

 

[onedaywhen]

You need to know the name of the column. To find this you could get an
empty recordset based on the original query i.e.

SELECT * FROM [Table1$A:E] WHERE 0=1

Enumerate the recordset's Fields collection to determine the column
name, then plug this back into the WHERE clause of your original
query.

Use a connection string that looks like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\MyWorkbook.xls;Extended Properties=Excel 8.0

In other words, use the OLE DB provider for Jet.