Home |
Search |
Today's Posts |
#1
|
|||
|
|||
AHHHH-Get Data from Multiple Excel workbooks
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 |
#2
|
|||
|
|||
AHHHH-Get Data from Multiple Excel workbooks
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 |
#3
|
|||
|
|||
AHHHH-Get Data from Multiple Excel workbooks
Query should get all rows in a sheet, provided they are contiguous.
Gary "JAA149" wrote: 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 |
#4
|
|||
|
|||
AHHHH-Get Data from Multiple Excel workbooks
4 - How do I create a query in Excel (MS query) from the Data Import
External Data New Data Base Query command. Select Data Import External Data New Data Base Query. Choose excel file as your datasource and select the workbook with your data. In the Add Tables box select the options button and check the system table box and your sheets should be listed. Select your sheet, select which columns and return data. "JAA149" wrote: 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 |
#5
|
|||
|
|||
AHHHH-Get Data from Multiple Excel workbooks
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 |
#6
|
|||
|
|||
AHHHH-Get Data from Multiple Excel workbooks
Hi
When you query an excel table, the excel workbooks is for datasource, and named ranges are for tables. I haven't tried it out, but is it allowed to create a query with 2 different datasources simultanously at all? As about using named ranges in queries: the only restriction I have encountered, is that you must define the range as reference - no formulas are allowed . For such tasks as yours, I myself have used links combined with query: Create a sheet p.e. Links Into 1 row enter table headers from 2nd row, start with links to 1st source table, like =IF('C:\My Documents\Data\[Orders 2005.xls]Sheet1'!A1="","",'C:\My Documents\Data\[Orders 2005.xls]Sheet1'!A1) Copy the formula to range, which includes all columns, and a number of rows big enough to include always the whole Orders2005 table Below this range, continue with links to other table, like =IF('C:\My Documents\Data\[Orders 2006.xls]Sheet1'!A1="","",'C:\My Documents\Data\[Orders 2006.xls]Sheet1'!A1) Again copy the formula to range, big enough. On sheet Links, you get yoined table with a lot of empty rows. You have to estimate now column(s), which always determine an non-empty row, when not empty. Create a named range p.e. LinksTbl, which includes all cells with formulas on sheet Links On another sheet, create a simple ODBC query with same workbook as datasource (you must save the workbook before), and range LinksTbl as source table, where in WHERE clause you set key column to be Not Null (or any of key columns to be Not Null, i.e. Column1 Is Not Null Or Column2 Is Not Null Or ...). The query must retrieve all rows from sheet Links, except empty ones. Hide the sheet Links, as no user intervention is required there. Here you go! Arvi Laanemets "JAA149" wrote in message ... 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 | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
Help with Macro (copying data from multiple workbooks) | Excel Discussion (Misc queries) | |||
How do I center data across multiple cells in excel without mergi. | Excel Discussion (Misc queries) | |||
how do I insert multiple rows in excel after every row of data | Excel Discussion (Misc queries) |