469,359 Members | 1,663 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,359 developers. It's quick & easy.

ASP and Excel

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
Jul 19 '05 #1
3 5579
Thies Meier wrote:
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
Jul 19 '05 #2
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
"Thies Meier" <tm@sh-home.de> wrote in message
news:bj*************@news.t-online.com...
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

Jul 19 '05 #3
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.

"Thies Meier" <tm@sh-home.de> wrote in message news:<bj*************@news.t-online.com>...
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

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
6 posts views Thread by Matthew Wieder | last post: by
14 posts views Thread by pmud | last post: by
22 posts views Thread by Howard Kaikow | last post: by
7 posts views Thread by Alain \Mbuna\ | last post: by
9 posts views Thread by Doug Glancy | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.