Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
JAA149
 
Posts: n/a
Default 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   Report Post  
Arvi Laanemets
 
Posts: n/a
Default 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   Report Post  
Gary Rowe
 
Posts: n/a
Default 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   Report Post  
Gary Rowe
 
Posts: n/a
Default 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   Report Post  
JAA149
 
Posts: n/a
Default 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   Report Post  
Arvi Laanemets
 
Posts: n/a
Default 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
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
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
How do i copy columns of data in notepad into microsoft excel? Jason Excel Discussion (Misc queries) 1 February 11th 05 12:05 AM
Help with Macro (copying data from multiple workbooks) Tim Harding Excel Discussion (Misc queries) 1 February 5th 05 11:37 PM
How do I center data across multiple cells in excel without mergi. lmark Excel Discussion (Misc queries) 6 January 27th 05 06:55 PM
how do I insert multiple rows in excel after every row of data grantm5 Excel Discussion (Misc queries) 1 December 14th 04 09:09 PM


All times are GMT +1. The time now is 11:34 AM.

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"