Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Background AHHH? | Excel Discussion (Misc queries) |