Hey Ron,
Thanks for the assistance. Very nice sample file.
However, the Excel file referenced in the sample isn't in the .zip. Do you
have that sample data Excel file?
In answer to your question about referencing the sheet, unfortunately, for
my use case scenario, this wouldn't work. I'm being handed a file that has
a singular spreadsheet containing all of the tables but no named ranges.
I'll probably have to create the names myself. Is there no way to work with
the 'CurrentRegion' object and pass that as the source range?
Thanks Again,
Todd
"Ron Coderre" wrote in message
...
See if the QueryMaster file at Debra Dalgleish's website gets you pointed
in
the right direction:
http://www.contextures.com/excelfiles.html#External
Use the Generic Excel List Query
Assuming you want to use the QueryMaster file as the data source, I
inserted
a new worksheet (Sheet1) and copied your posted data to it.
Query Field Settings of Interest:
Data_Source: (enter the complete file path and file name )
SQL_Select: SELECT Name, Year, Earnings
SQL_From: "FROM `C:\ExcelQueries\QryMaster\QueryMaster`.`Sheet1$`"
SQL_Whe WHERE Name='Company1'
Note: The best approach is to create a named range for the data list and
use
that name in the SQL_From field. But, if you refer to the sheet name
followed
by a dollar sign (Sheet1$), the query will use the used range of the
referenced sheet and try to interpret it as a table.
Is that something you can work with?
Post back with any questions.
***********
Regards,
Ron
XL2002, WinXP
"TCook" wrote:
Hey All,
I'm trying to run a SQL statement against an Excel range. For example,
say
we have a range with the following:
Name Year Earnings
Company1 1999 $25,000
Company2 1999 $30,000
Company3 1999 $35,000
Company1 2000 $27,000
Company2 2000 $32,000
Company3 2000 $37,000
Company1 2001 $29,000
Company2 2001 $34,000
Company3 2001 $39,000
And want to do something like:
SELECT Name, Year, Earnings FROM Selection WHERE Name='Company1'
The ADO code samples that I found do not have complex SQL statements and,
more importantly, do not work.
Thanks in Advance,
TC