Getting external data from multiple excel workbooks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)Data>Import External Data>New Database Query
Databases: Excel Files
Click to expand...

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 Data>Refresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)


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

XL2002, WinXP
 
Back
Top