SQL Statements for Excel spreadsheets

Ragman

17/02/2003 at 08:04

Ive got an excel spreadsheet with a sheetname of 'members' and a load of columns named MembName, MembNumber, PostCode....etc (the first row in sheet has these as column names).

I cannot write an sql statement that will retrieve data based on a WHERE clause, but only a * statement.

So i can do "SELECT * FROM members" But Not "SELECT * FROM members WHERE MembName = 'bert';"

any ideas??

Gruntie

17/02/2003 at 10:22

Look at the results from the unfiltered query. Are the column names listed in the rows?

Ragman

17/02/2003 at 12:58

Gruntie wrote

Look at the results from the unfiltered query. Are the column names listed in the rows?

They are indeed mate which is why i do not understand why i cant use the WHERE Clause

Gruntie

17/02/2003 at 14:38

Well there's your problem the datasource does not understand (at the moment) that the first row contains headings

I can't really help you any further as I am not a XL wiz but the problem seems to lie in the definition of the datasource and not with your SQL

Try defining the data as a named range and see if that works

18/02/2003 at 11:59

Andy.. I happen to have in the boot of my truck, a book on SQL for Excell etc.

Shall I give it to Hunter or you want to swing over and get it? If not, guess I can look thru' and help out.

Ragman

18/02/2003 at 12:07

lustywench wrote

Andy.. I happen to have in the boot of my truck, a book on SQL for Excell etc.

Shall I give it to Hunter or you want to swing over and get it? If not, guess I can look thru' and help out.

Cheers Lusty, this is a one time only thing that is being written. It does not matter to me or customer if first row does or does not have column/field names. So if you can scan thru your book to see what is says on using WHERE clauses in SQL statements for excel, that would be fantastic

The worksheet name will always be called Members, so that is my table name.

Gruntie

18/02/2003 at 12:22

Ragman wrote

It does not matter to me or customer if first row does or does not have column/field names.

Although you may not care about them it does matter because if they are in the returned dataset they are not being used to define the column names which is why the where clause is failing.

Hope that helps define your problem - over to lusty for a solution

Gruntie

18/02/2003 at 12:30

Where are you defining the connection to the spreadsheet? ie is this code written in VB/Access/Excel ?

Ragman

18/02/2003 at 13:13

Gruntie wrote

Where are you defining the connection to the spreadsheet? ie is this code written in VB/Access/Excel ?

ASP vb

18/02/2003 at 13:36

Raggy...do you have this already?

SELECT members.MembName
FROM (whatever path req)members

The book says you need brackets round the selection criteria:

SELECT members.MembName
FROM (whatever path req)members
WHERE [members.MembName ='bert']

Try that..if it doesn't work will have another bash.

Ragman

18/02/2003 at 14:49

lustywench wrote

Raggy...do you have this already?

SELECT members.MembName
FROM (whatever path req)members

The book says you need brackets round the selection criteria:

SELECT members.MembName
FROM (whatever path req)members
WHERE [members.MembName ='bert']

Try that..if it doesn't work will have another bash.

I have just managed to get it working, even though i have not changed anything!! I hate it when that happens.

I also just found an example similar to your one lusty, so i will try that as well.

Thanks very much

Ragman

18/02/2003 at 15:26

shit, it stopped working again!!

right, MembName is my worksheet name and membno is the first column, so does below look ok? Or should i only include the path and not the path and filename? and should the path be in quotes??

SELECT *
FROM (c:\andy.xls)MembName
WHERE [MembName.membno ='bert']

I think im being dim

Gruntie

18/02/2003 at 21:23

mind you 30 sec on google answers most questions

try "ADO Excel Connection"

objExcel.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & <Path and filename> & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""

Watch out for the HDR argument.

If you set this argument to YES you indicate that the first line in your Excel sheet is a header row.

19/02/2003 at 09:30

Andy if it works then doesn't work that suggests that something else may be amiss...that the code is ok else it wouldn't work at all. Maybe there is a corruption on the worksheet.

Can I ask why you haven't done this in Access? It is soooo much easier!!

Your say

Terms and Conditions | Code of Conduct

Email me when a response is made?

Bike reviews

Classifieds