Hi everyone,

I would like to use Get external data in the Data tab to query data from
mulitple workbooks. By doing so, i supposed, there is no need to open the
workbooks and still get the data that i would like to have. The data from
these workbooks appears in the same format but it is not in a template/table
form.

However, I can only manage to query from a single file. How do I query and
collate all the data from mulitple excel files into a single worksheet?

Thanks in advance!

You can use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts. This also works for consolidating data from the
active workbook (Just save it first so Excel can find it):

This example uses 5 named ranges in 5 different workbooks.
(Each range contain 4 columns: Dept, PartNum, Desc, Price)

Assumptions:
The data in each wkbk is structured like a table:
---Col headings (Dept, PartNum, Desc, Price)
---Columns are in the same order.

The data in each wkbk must be in named ranges.
---I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
---You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
....ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query
Databases: Excel Files

Browse to one of The files, pick The data range to import.
---Accept defaults until the next step.

At The last screen select The View data/Edit The Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\Dept1111`.rng1111Data
UNION ALL
SELECT * FROM `C:\Dept2222`.rng2222Data
UNION ALL
SELECT * FROM `C:\Dept3333`.rng3333Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng4444Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng5555Data

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )
Return the data to Excel.

Once that is done....to get the latest data just click in the data range
then DataRefresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Dean C" wrote:

Hi everyone,

I would like to use Get external data in the Data tab to query data from
mulitple workbooks. By doing so, i supposed, there is no need to open the
workbooks and still get the data that i would like to have. The data from
these workbooks appears in the same format but it is not in a template/table
form.

However, I can only manage to query from a single file. How do I query and
collate all the data from mulitple excel files into a single worksheet?

Thanks in advance!

Maybe you can use my ADO example
http://www.rondebruin.nl/ado.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dean C" wrote in message ...
Hi everyone,

I would like to use Get external data in the Data tab to query data from
mulitple workbooks. By doing so, i supposed, there is no need to open the
workbooks and still get the data that i would like to have. The data from
these workbooks appears in the same format but it is not in a template/table
form.

However, I can only manage to query from a single file. How do I query and
collate all the data from mulitple excel files into a single worksheet?

Thanks in advance!

Hi Ron, I found your answer which is perfect for something I need to do but
.... I need just one further hep: how can I add a specific field in SQL with a
text, for example the file's name.

I tried something like:

SELECT range.*, "Text" as Heading FROM `excel file name`.range

but does not work... can you pls help me out? thanks. Kind regards.

Santiago

"Ron Coderre" wrote:

You can use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts. This also works for consolidating data from the
active workbook (Just save it first so Excel can find it):

This example uses 5 named ranges in 5 different workbooks.
(Each range contain 4 columns: Dept, PartNum, Desc, Price)

Assumptions:
The data in each wkbk is structured like a table:
---Col headings (Dept, PartNum, Desc, Price)
---Columns are in the same order.

The data in each wkbk must be in named ranges.
---I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
---You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query
Databases: Excel Files

Browse to one of The files, pick The data range to import.
---Accept defaults until the next step.

At The last screen select The View data/Edit The Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\Dept1111`.rng1111Data
UNION ALL
SELECT * FROM `C:\Dept2222`.rng2222Data
UNION ALL
SELECT * FROM `C:\Dept3333`.rng3333Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng4444Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng5555Data

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )
Return the data to Excel.

Once that is done....to get the latest data just click in the data range
then DataRefresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Dean C" wrote:

Hi everyone,

I would like to use Get external data in the Data tab to query data from
mulitple workbooks. By doing so, i supposed, there is no need to open the
workbooks and still get the data that i would like to have. The data from
these workbooks appears in the same format but it is not in a template/table
form.

However, I can only manage to query from a single file. How do I query and
collate all the data from mulitple excel files into a single worksheet?

Thanks in advance!