Todd
the Excel file referenced in the sample isn't in the .zip
Sorry for the confusion...I didn't actually change the file at Debra's
site.
I described what I did to test a same-workbook query.
I'll probably have to create the names myself
Definitely
Is there no way to work with the 'CurrentRegion' object and pass that
as
the source range?
Not that I'm aware of. You'd need to create range names to be
referenced
by
the SQL.
I hope that helps
***********
Regards,
Ron
XL2002, WinXP
:
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
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_Where: 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
:
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