Help with ADO query on MS Excel worksheet ...

[Guest]

Hi,

I am trying to build a query to retrieve data from a closed workbook (as
seen on Ron De Bruins site and MSDN using the MS Excel Drivers for ODBC )

Here is the site:
http://www.rondebruin.nl/ado.htm

Anyway, I have no trouble at all with a simple query like this:
SELECT * FROM [Sheet1$A3:S100];

But how do I go about getting named fields or columns only?

I tried to create a query to retrieve only certain fields and using a WHERE
clause and it worked in MS Query, but in MS Excel in VBA it always fails with
''Microsoft Jet Engine cannot find the object 'Sheet1$' ... Make sure you
spell ... etc"

Can anyone give me any help on how I should be doing this:
SELECT `Sheet1$`.F2, `Sheet1$`.F13, `Sheet1$`.F19,
`Sheet1$`.F14, `Sheet1$`.F5, `Sheet1$`.F6,
`Sheet1$`.F8 & `Sheet1$`.F9 & `Sheet1$`.F10,
`Sheet1$`.F16, `Sheet1$`.F17,`Sheet1$`.F1
FROM `Sheet1$` `Sheet1$` WHERE (`Sheet1$`.F2='50061648')

You can see I want certain fields only, and a criteria - this query runs
fine in MS Query.

I have done far more complex things than this before, joining sheets in
complex queries and so on, but before I had a named range to work with.

thanks for any help or advice...

Philip

 

[Bob Phillips]

If its a named range, it is just like querying a table in a db

SELECT * FROM range_name;

To do a selective query, you should use a named range with a header row that
is the column names, and then query the column name

SELECT * FROM range_name WHERE Name = 'Joe' AND CtyCode = 'GB';

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi,

thanks Bob I got in in the end by retrieving the fields collection of the
recordset after it was open, then taking the field names I wanted:

SELECT [COB Date], Account, [Account Name], [Trade Type], [Buy/ Sell],
Quantity, Market, Product, [Contract Month], [Contract Year], [Put/ Call
Indicator], [Strike Price], [Contract Currency], [Trade Price], [Executing
Broker], [Clearing + Execution Commission (local)], [Total Fees (local)],
[Option Premium (local)], [Trade Date] FROM [Sheet1$A3:S100];

cheers

Philip

If its a named range, it is just like querying a table in a db

SELECT * FROM range_name;

To do a selective query, you should use a named range with a header row that
is the column names, and then query the column name

SELECT * FROM range_name WHERE Name = 'Joe' AND CtyCode = 'GB';

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi,

I am trying to build a query to retrieve data from a closed workbook (as
seen on Ron De Bruins site and MSDN using the MS Excel Drivers for ODBC )

Here is the site:
http://www.rondebruin.nl/ado.htm

Anyway, I have no trouble at all with a simple query like this:
SELECT * FROM [Sheet1$A3:S100];

But how do I go about getting named fields or columns only?

I tried to create a query to retrieve only certain fields and using a
WHERE
clause and it worked in MS Query, but in MS Excel in VBA it always fails
with
''Microsoft Jet Engine cannot find the object 'Sheet1$' ... Make sure you
spell ... etc"

Can anyone give me any help on how I should be doing this:
SELECT `Sheet1$`.F2, `Sheet1$`.F13, `Sheet1$`.F19,
`Sheet1$`.F14, `Sheet1$`.F5, `Sheet1$`.F6,
`Sheet1$`.F8 & `Sheet1$`.F9 & `Sheet1$`.F10,
`Sheet1$`.F16, `Sheet1$`.F17,`Sheet1$`.F1
FROM `Sheet1$` `Sheet1$` WHERE (`Sheet1$`.F2='50061648')

You can see I want certain fields only, and a criteria - this query runs
fine in MS Query.

I have done far more complex things than this before, joining sheets in
complex queries and so on, but before I had a named range to work with.

thanks for any help or advice...

Philip