P
Philip
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
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