J
JAA149
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
==============
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
==============