Hi
Define the table on Data sheet as named range, like
MyData=Data!$A$1:$X$1000
NB! Don't use dynamic ranges. Defined range must contain a single header row
at top. Having any number of empty rows at bottom is far better as having
them not enough (you always can use WHERE clause in query to get rid of
them). And you better avoid mixed data types in columns: format columns on
data sheet properly, and be sure that all formulas return right data type -
otherwise some data can be lost in query result table.
Be sure that Analysis Toolpack is checked as Add-In. I don't know why, but
otherwise you have problems with queries to Excel tables - especially when
source data is in another workbook. Save the workbook.
Create an ODBC query. As datasource you will have your Excel workbook, the
named range will serve as a table. Headers in top row of table determine
table field names.
To avoid sitiation, where the new query is running before the query on Data
sheet has returned new data, disable background refresh in query properties,
and maybe use workbooks Open event to start queries in right order.
PS. Why not to create the second query also directly from SQL database. I
don't belive it is not possible to determine WHERE clause or use aggregate
functions with SQL ODBC driver.
--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )
"Preacher Man" <nospam> wrote in message
news:uaua%23uhJGHA.2828[ at ]TK2MSFTNGP12.phx.gbl...
[Quoted Text] >I have a need for some query help and I will try to explain my situation.
> Thanks in advance for any help.
>
> I have an Excel spreadsheet that has two different sheets. One one sheet
> called "Data" I have created a query that pulls AP Invoices from from a
> SQL
> database. In the results of this query I may have several lines with the
> same Company.
>
> Ok here's my question. On my other sheet page of the same file, is there
> any way to run a Select statement and use the "Data" sheet as the FROM
> Source? For example I would like to do something like this:
>
> Select Distince Company, Invoice, Amount FROM DATA!Range
>
> I know this is very confusing, I just hope someone can decipher what I
> have
> written.
>
>
>
>
|