Hi

You can't use dynamic named ranges as source for ODBC query. Define for this
purpouse non-dynamic named ranges (like SoueceTbl1=SheetName!$A$1:$X$1000)
with enough rows to have all data always included, and in query use the
WHERE clause to filter out empty rows.

When in your source table you often delete a lot of rows, such fixed named
range may decrease. When this is the case, then you have periodically to
check them, or you can write p.e. workbooks Close event, which redefines the
named range every time the workbook is closed.

Another way is to define the source table in query wizard as reference to
range?/sheet?. I myself have never used this technique, so all I know about
this is, that it's possible. But I'm almost sure you can't use dynamic
ranges this way too.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"JAA149" wrote in message
...
Dear All,
I am going down real bad.
1 - I have many excel workbooks. Let's say around 30.
2 - Each workbook contains only 1 sheet or may in the future contain more
sheets.
3 - Each sheet contains list of data with dynamic named ranges which
expand
as new data is entered.
4 - How do I create a query in Excel (MS query) from the Data Import
External Data New Data Base Query command.
5 - I tried everything. Nothing works. I can not add a second excel
workbook
while I want to add as many workbboks as I want say 30. The Owner & Table

Add Table Workbook options in MS Query become disabled. How do I add
other
workbooks?
6 - I want to use the data for Pivot tables for multiple consildation
ranges.
7 - The problem is it can be used for multiple sheets in a single workbook
by using Join in the MS Query. I want to use multiple sheets in multiple
workbooks for the data source.

Help will be highly appreciated.

Regards