#1   Report Post  
JAA149
 
Posts: n/a
Default AHHH! Again

Dear Arvi,

Some how I had guessed that dynamic named ranges can not be used in MS Query
- ODBC Query or SQL specific Query but you have confirmed it. So I tried an
alternative solution but again AHHHHHHHHHHHHHHH!

1 - I have Excel workbooks named Orders 2005, Orders 2006.
2 - I name the ranges normally as rngOrd05 & rngOrd06. (I suspect sheet
names are not important or they are not used to refer to the ranges, either
global names or local names??)
3 - Create a `UNION ALL` query (name of the Query file is All Orders Query)
to join the data to get a query of all the orders for both the year 2005 &
2005 with 'WHERE (rngOrd05.SrNo Is Not Null) & (rngOrd06.SrNo Is Not Null) to
not include the empty rows (SrNo is a labeled of column in both the ranges)
4 - I return this data to Excel which & create a work book 'Orders Database'
based on the Query 'All Orders Query'. In this workbook the data is linked to
the workbooks Orders 2005 & Orders 2006 through the query. Also in this
workbook 'Orders Database' a range is named automatically with the name of
the query file. So I have a named range as 'All_Orders_Query'. This range is
some how dynamic as it changes when new data is entered in Orders 2005 &
Orders 2006.
6 - Do steps 1 to 5 for 'Invoices'
7 - THE PROBLEM:- The ranges created by MS Query as 'All Orders Query' in
the Orders Database & 'All Invoices Query' in the Invoices Database are not
recognized by MS Query again as I want o create a third query of LEFT OUTER
JOIN to see which orders have which invoices issued for them.

So again the same problems comes as MS Query does not recognize the named
ranges it itself creates. And also if we rename the ranges or create them
manually, the data will no longer be linked to the other workbooks.

Regards

JAA

==============

"Arvi Laanemets" wrote:

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Background AHHH? beefyme Excel Discussion (Misc queries) 1 January 14th 05 06:35 PM


All times are GMT +1. The time now is 09:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"